Overview

Given a database design specification, implement it by creating a database in Microsoft Access. This "spec" file is named PremiereCompany_DB_Specification.docx and is available along with this document in the assignments area of D2L.

Requirements

1. Following the database design specification build the Access database.

Name this file YLN_A4.accdb, substituting your last name for YLN.

2. Create five tables according to the database design specification document:

Use design view to create the tables. Name the tables and columns as specified in the spec.

Give each column the datatype provided in the spec.

Designate a primary key for each table as shown in the spec

3. Create relationships among the tables according to the ERD provided in the specification document.

Be sure to use the foreign keys as specified in the design.

All relationships should enforce referential integrity.

4. Implement the business rules as appropriate in your database.

Many of the business rules in the design document are already incorporated into the design. The ones for which you need a constraint in your database have a hint given in the design document.

5. Add all rows of the sample data to the tables.

Use the datasheet view to add the data.

Insert the data into the tables in this order: SALESREPS, CUSTOMERS, ORDERS, PARTS, ORDERLINES.

You may choose to add more rows to your tables but remember to keep referential integrity: insert the parent before inserting the child.

Home | MyPCc (E) A4-Database Impleme. x _ c Secure l https://online.pcc.edu/d21/le/dropbox/194486/333320/DownloadAttachment?fid=7001944 : Apps CC Library PCC Home O sparkRef r PCC Library Search Wireless Printing at : EasyBib Database Specification Business Rules 1) Every customer has one sales representative 2) A customer can place many orders. 3) An order can have many parts on it. 4) The quote for a particular part on an order may be different than the part's unit price 5) The class a part belongs to is one of the following: (HW, SG, AP). Hint: Create a lookup with combo box listing these selections. 6) All parts of the same kind (i.e., the same part id) will be stored at the same warehouse. 7) Part IDs are uniquely assigned by Premier Co staff 8) There are 3 warehouses, with numerical ids, and the listed warehouse in the PART table must be no less than 1 and no greater than 3. Hint: Use a validation rule here ERD SalesReps Customers Orders OrderLines Parts Data Listing Attribute # ReplD LName FName Street City StateCode Zi Data Type AutoNumber Short Text Short Text Short Text Short Text Short Text Short Text Currency Number Field Size & Other Notes Unique identifier set by DBMS 30 30 80 30 Entity SALESREPS lp Commission Rate DECIMAL(6,2) 1:05 PM 2/22/2018 Go Home | MyPCc (E) A4-Database Impleme. x _ c Secure l https://online.pcc.edu/d21/le/dropbox/194486/333320/DownloadAttachment?fid=7001944 : Apps CC Library PCC Home O sparkRef r PCC Library Search Wireless Printing at : EasyBib Database Specification Business Rules 1) Every customer has one sales representative 2) A customer can place many orders. 3) An order can have many parts on it. 4) The quote for a particular part on an order may be different than the part's unit price 5) The class a part belongs to is one of the following: (HW, SG, AP). Hint: Create a lookup with combo box listing these selections. 6) All parts of the same kind (i.e., the same part id) will be stored at the same warehouse. 7) Part IDs are uniquely assigned by Premier Co staff 8) There are 3 warehouses, with numerical ids, and the listed warehouse in the PART table must be no less than 1 and no greater than 3. Hint: Use a validation rule here ERD SalesReps Customers Orders OrderLines Parts Data Listing Attribute # ReplD LName FName Street City StateCode Zi Data Type AutoNumber Short Text Short Text Short Text Short Text Short Text Short Text Currency Number Field Size & Other Notes Unique identifier set by DBMS 30 30 80 30 Entity SALESREPS lp Commission Rate DECIMAL(6,2) 1:05 PM 2/22/2018 Go