- Pick a company or business from the following list. This will be the basis for your entire final project. If you have a company or business that is not listed, get approval from your instructor.
- Retail Business – Possible Ideas: Clothing, Computer, Plumbing, Auto Supply, Stationary, Furniture, Shoes
- Service Company – Electrical, Heading, Air Conditioning, Lawn Care, House Remodeling
- Theme Park
- General Construction Contractor
- Modeling Agency
- Your final project MUST include all of the following items:
- Create a Word table including data for your business of choice. Identify information that is, or could be, regarded as important to the company for which the database is being designed. For example, if you have a database design for a production environment, then information that would be important to this company might be total production per day, total production by department for a given period, or a comparison of production and sales from one month to the next. This information will be the basis for queries, forms, and reports in the development stage of your project. All of these items can be completed in a Word document format.
- Create dependency diagrams for all entities (tables) in your database. Be sure that each entity (table) is in 3NF and includes a relational. These cannot be hand-written/drawn; use Word, PowerPoint, or MS Visio via the XenDesktop to accomplish this.
- Complete MS Visio Entity-Relationship Diagram (ERD) for the database design. Include all maximum and minimum cardinalities on your diagram. Be sure to identify all primary and foreign key fields. The ERD CANNOT be hand-drawn; you must use MS Visio via the XenDesktop to create this diagram.
- You must include all business rules that will affect your database design. Business rules are constraints, or restrictions that limit how you develop your database. For example, in a sales-oriented database, quantities purchased cannot be negative. Identify all business rules that you believe will affect your database design. Also, decipher the relationships in your ER diagram in written form.
- Access DBMS – Create an Access database structure using the normalized data found in your 3NF dependency diagrams and your Visio ER diagram.
- Build tables including primary and foreign keys, data fields with specific data types, and notes in each field in design view explaining the purpose of the data. For instance, you have a table named EMPLOYEES, one field is named EMPLOYEE_ID, its data type is number, and its description is primary key unique number given to each new employee.
- Add at least five records to each table using the datasheet view.
- Create relationships between tables. Remember when creating a 1:M relationship between two tables you need to enter the primary key field of the parent into the child table as a foreign key field. If you have a M:N relationship, you need to create a linking table containing a composite primary key field with two 1:M relationships connecting to it.
- Create at least two queries that contain constraints.
- Design at least one form.
- Create at least two reports. One of these reports must be based upon one of your queries.
Please refer to the Unit 1 project attachment below to answer these questions.
Final Project Part 1 – Week 3: Sections a and b – table data, any business information, dependency diagrams (including relational schemas) normalized to 3NF. All items can be submitted in a Word document format.
Final Project Part 2 – Week 5: Sections c, d, and e – Visio ER diagram, written business rules, Access DBMS containing all items as seen in the project instructions. You will submit a .vsd or .vsdx file for your ERD, Word document for your business rules, and .accdb for your Access DBMS.
Data table and business information
Dependency diagrams and Relational Schemas to 3NF
Visio ER diagram
Access DBMS – tables, fields, relationships, queries, form, and reports