Please help me with the questions below, I am using Oracle Data Modeler for this assignment:

The objective of the assignment is to develop a database to support an on-line auction site called Nifty Auction. The auction site allows sellers to list items and buyers to purchase and bid on items. The following provides a high-level description of the required components of the auction system:

The auction site stores information about clients (buyers and sellers) and items up for bid.

Items are sold by sellers and bid-on by buyers. Buyers may create watch lists for items.

Feedback about buyers and sellers is stored by the system.

Note: Application software will NOT be developed for this assignment, and no user interfaces, reports, calculations, or business logic will be written. Focus only on the data and information requirements. In other words, you will be creating the database that supports the application not the application itself.

Tasks: Using the business description, business rules and screenshots provided on the following pages, use the SQL Developer Data Modeler software to complete the following five (5) tasks:

TASK 1: External Schemas (Sub-Views)- Create a new Data Modeler file. When you save your model, give it a meaningful name (i.e. NiftyAuction) Develop an E-R Diagram with Sub-Views (External Schemas) as indicated on pages 3-4 of this document using Information Engineering notation. Each subview represents an external schema. You will need a minimum of five (5) Data Modeler logical Sub-Views as described on the following pages. The Logical Model in the Data Modeler will represent your Conceptual Schema for your design. You may develop additional Sub-Views if desired.

Set the Notation to Information Engineering for your Logical model and sub-views

The same entity may appear in more than one Sub-View in your model.

Attributes added in one Sub-View will appear in all Sub-Views containing the shared entity. To add an existing entity to a Sub-View: Expand the Logical Model in the Navigator pane; then expand the Entities; then drag and-drop the required entities into the Sub-View.

TASK 2: Conceptual Schema (Logical Model) - After all Sub-Views are created, return to the Logical Model and organize the entities and relationships until all items are visible. Ensure all data requirements are supported by your model and all relationships are defined correctly.

TASK 3: Internal Schema: In this task, you will transform the Conceptual Schema (Logical Model) the project to an internal schema in which you define each table, making sure to specify the primary keys and whether or not a given column has a NOT NULL constraint.

1) Make sure that each entity has a primary key.

a) Determine primary keys for each entity. You can select an existing attribute or combination of existing attributes. Alternatively, you may want to create a new attribute that will function as the primary key for each entity.

b) Notice that the Data Modeler software SHOULD HANDLE all of the transforming of relationships to foreign key attributes for you. If you modeled attributes that have been replaced by foreign keys, be sure to delete the modeled version of the attribute. If you dont see a foreign key in a related entity, double-click the relationship line then close the relationship properties window. This should update the foreign key. Some foreign key additions are not automatically updated by the software.

2) Remove all many-to-many relationships and implement relationship (composite) entities for any many-to-many relationships that may exist in your logical schema.

TASK 4: Creating a Data Dictionary (Defining and Assigning Attribute Domains and Data Types) A data dictionary will be defined from within the Data Modeler software. This data dictionary will specify the domain for each table column. Some attributes will not have user-defined domains they should be assigned a valid Oracle datatype.

1. Define at least 5 specific domains with validation rules. One example would be to add a domain for a US State abbreviation. The abbreviation must be 2 characters and must match one of the listed values. Note: You only need to list 5 state abbreviations for this assignment.

2. EVERY attribute MUST be assigned to a domain or data type. Please note that many attributes share domains (e.g. yes-no, state attributes, etc). It is a good idea to define the domain once, and re-use it multiple times for attributes sharing that domain.

3. If an attribute does not have a user defined domain, ensure the attribute has been assigned the appropriate data type. For example, if the attribute is a date value, assign the Date data type to the attribute. Do not use a general character string for a date.

4. When creating domains, be sure to specify a data type, and for character string data types, you must also specify a size (maximum number of characters allowed).

5. You MUST define and apply at least 5 specific domains in your Data Modeler file. 6.

Recommended Data Types for attributes:

Variable length strings:

VARCHAR2(size)

Date; Date

Fixed length strings: CHAR (size)

Floating Point Values and Currency: Float

Whole Numbers: Integer

Times: (recommended) VARCHAR2(size)

Date with Time Timestamp

TASK 5: Table Creation Script (Database Schema) Using the Oracle Data Modeler software, create an Oracle script file (DDL file) containing all the SQL statements necessary to create the tables defined in your ER model. In grading your assignment, I will run this script to create your tables.

Data Modeler Sub-View 1: Clients Buyers and Sellers: Clients Can be Buyer, Seller or Both Buyers and Sellers have many attributes in common: Unique ID Number (not shown on screen shots), username, password, first name, last name, screen name (seller name/store name or individual buyers screen name), contact e-mail, contact street address, contact city, contact state, contact zip code, contact phone number (optional), date joined, Type of client (1: buyer, 2: seller, 3: both) Seller: Specific attributes: Seller rating, percent positive feedback, number of items sold, number of views, seller location country Buyer/Bidder: Specific attributes: Buyer rating, number of items purchased, current balance due (total), automatic payment method (CC, Paypal)

Data Modeler Sub-View 2: Items for Bid (see screen shots for more information): Sellers may sell many items but each item is sold by a single seller

Buyers may bid on and purchase many items

Each item purchased is considered a single sale,

Each item is sold by one seller and purchased by one buyer.

Data requirements include the values on the 2 screen shots shown below and include the following values: ID number of the seller, Item ID, Title or short description of the item, Starting Price, Shipping Cost, auction end date and time - you do not need to store minutes left in the auction, Minimum bid amount, current number of bids, Condition (New, Used, Not Specified, NWOT =>Note: NWOT = New Without Tags) Items for Bid Screen Shot Summary View: Continued next page ISQA 3310 - Managing the Database Environment Project Part 1 Data Modeling and Entity-Relationship Diagramming Items for Bid Screen Shot Detail View: Note: You do not need to store the time left in the auction, the estimated delivery date, or the information about following a seller.

Data Modeler Sub-View 3: Feedback for Buyers and Sellers: Feedback: Both buyers and sellers may have multiple feedback records. Each feedback record will contain the following information: Type of feedback (buyer or seller), Client ID posting the feedback (also called From), Client ID receiving the feedback (also called To), Feedback Comments, Positive-Negative indicator (P, N), Date of feedback, and the Item ID Number the feedback applies to). For each item purchased (as a final sale for an item, 2 feedback records may be stored. One left by the buyer and one left by the seller. Feedback Screen Shot: Note: This screen shot shows only feedback as a buyer, however, sellers may also have feedback records. In place of the Seller name shown below, the From value will contain the word Buyer: followed by the screen name of the buyer.

Data Modeler Sub-View 4: Buyer Watch List Buyer Item Watch List: Items a user wants to watch Buyers may have many items on their watch list and items may be on many buyers watch lists.

Data Modeler Sub-View 5: Buyer Bid List/Purchase Purchase/Bid: When a buyer places a bid, a bid record is created. The bid record contains the following information: ID of the client placing the bid, Item ID of the item to be purchased, date of bid, amount of bid, win or loss status of item (Note: This value is optional will only be filled out after the auction closes)