Database Project wk8
Extra Credit for Database Project wk8
You designed a database in your Term Project. Now implement the 3NF tables in MS Access, use the same Table and Field names as your design, set the primary keys, and field types as described in your term project.
Here are the detailed requirements:
1) Create a “NEW” MS Access Database that matches your 3NF submission – ensure it properly reflects the 3NF Tables, Primary Keys, Fields, Foreign Keys, and relationship(s) that were designed (40%)
2) Populate the fields of each table in the database with at least 4 data sets (4 rows) (20%)
Include sufficient data to demonstrate the one-to-many or many-to-many relationship in the submitted project design.
3) Create 1 Query (use the question proposed in the Term Project), it must use at least 2 tables and the relationship identified described in the design (30%)
4) Create 1 Report (you may choose the content) (10%)
Include the below numbering scheme in the submission. DO NOT include the Questions or other content from the instructions:
0) Was the Term Project topic approved Yes or No (minimum 15% deduction, if not approved)
1) (5%) Term Paper Abstract (include the purpose of this “database”, explain one query – you expect the database to be able to process – the query must require the use of information from more than one entity)
2) (5%) Who will use, and who will administrator the database.
3) (10%) Explain the design methodology / approach you followed – refer to methods in our text.
4) (15%) Describe the initial entities (tables), and attributes (fields). State what a row represents in each table, and for each field: describe what the contents of each field represents, the type of field, and any constraints that you would like to see applied. Identify and describe the field(s) you believe could be the primary key for each entity.
5) (15%) Using the functional notation shown on pages 410-413 Examples 14.2, 14.3, 14.4 and 14.5, include a functional dependency analysis, ensure all fields in the database are included. Define functional dependency and explain one row of the functional notation using plain English, and fields from your project to demonstrate your understanding. SEE NOTE1
6) (10%) Using the table names, state what a row represents in each table, and explain the one-to-many or the many-to-many table relationship included in your design. SEE NOTE3
7) Design (it is possible that the same table(s) are in 1NF, 2NF, and 3NF – in which case repeat the tables for each normal form level and explain why they meet that definition)
a) 1NF Assessment (10%)
Copy the 1NF definition from the text (include quotes and page number)
Using relational schema (table) notation (see page 111 Figure 4.2.6) or spreadsheet format (identify the primary keys), submit the table(s) that represent the 1NF table(s) for your term project.
Explain how each table meets the 1NF definition (use table and field names)
b) 2NF Assessment (10%)
Copy the 2NF definition from the text (include quotes and page number)
Using relational schema notation or spreadsheet format (identify the primary keys), transform (if necessary) and submit the table(s) that represent the 2NF table(s) for your term project.
Explain how each table meets the 2NF definition (use table and field names)
c) 3NF Assessment (10%)
Copy the 3NF definition from the text (include quotes and page number)
Using relational schema notation or spreadsheet format (identify the primary keys), transform (if necessary) and submit the table(s) that represent the 3NF table(s) for your term project.
Explain how each table meets the 3NF definition (use table and field names)
8) (10%) writing style, organization of submission, clarity, APA usage
Submit a Word compatible document. Name your document Last Name_TermProject (i.e. Smith_TermProject).
Contact the instructor with any questions.
NOTE1: Generic functional notation examples:
Field1 -> Field2
Field1 -> Field2, Field3
Field4, Field5 -> Field6
NOTE2: You can find an example of relational schema – table notation on page 111 immediately under 4.2.6 label which shows the relational schema for part of the Dream Home database.
The basic table notation structure is: Table-name (field, field1, field2…) underline the primary fields.
DO NOT INCLUDE SPREADSHEET FIGURES
NOTE3: Review the Chapter 12 Study Aid document, Table Relationships, and the Parts and Suppliers examples in the Terms and Concepts Forum – if you need to refresh your knowledge base.
HINT: review your tables – for this exercise, 2 tables should not have the same primary key “and” keep in mind that tables will need to be linked (to satisfy the one-to-many or many-to-many relationship requirement), if we start with a single unnormalized or 1NF table – the normalization process ensures the needed links are included – often we start with more than one table, and while that is OK – we need to ensure tables are properly linked (sometimes using foreign keys, and sometimes using a junction table)