1. Create the four tables using specific data (see below) by creating the tables in ACCESS and key in these data as shown below (screen shots)
2. Create the four forms from the four tables in ACCESS
3. Create two queries, you may want to use the wizard to do this

4. Create two reports from the two queries
Attachments:

access_database_template.accdb

cis_310_access_assignment_instructions_updated1.docx

This will be the first of 8 individual assignments that you’ll be doing throughout the semester to create your own database. These assignments will be handed in and graded, as will the final version of the database.

This assignment is extremely important, because you need to make sure your database is designed correctly before you start actually creating it and populating it with data. So please spend an adequate amount of time on this.

NOTE: This is the first Final Database assignment, but it’s the second assignment of this semester. Therefore, the name that you will need to attach to the file will reflect the number of the week. See below for the precise naming method I would like you to use.

Here’s the assignment:

Read through the contents of the Word document named Final DB Project Description. (It’s located in the Class Data Files sub-folder, and it is also provided as the last lesson this week.) This file will provide you with all the information you need to start laying out your final database.
Based on the objectives described in the Final DB Project Description, plan out your database.

Decide how many tables you will need, and which fields will be in each one of the tables. Make absolutely sure your tables are fully normalized, so that there is no redundancy.
Specify the data type for all fields. Use the Leszynski Naming Convention for all table and field names.
Determine which relationships will need to be created, as well as the type of relationship. Clearly indicate the type (i.e., One-to-One, One-to-Many) for all proposed relationships. You should avoid Many-to-Many relationships for this database. That type of relationship falls outside the parameters of this class and isn’t necessary in this file.
Decide which fields will be the primary key fields and which ones will be the foreign key fields of the different proposed tables.
Indicate which fields will be connected to which fields in the relationships. Make sure it is clear to me when I look at your tables which fields will be connected to which other fields, as well as what you are proposing as the type of join.

The best way to do this would be by drawing lines to connect the fields. If you do this in Excel or Word, you can use the Line tool (available on the Drawing toolbar) or one of the AutoShapes (also on the Drawing toolbar), or you can simply write out a description of the different relationships. Regardless of your method, make sure I understand your objectives with regard to the different relationships that you’re proposing.

The following sample, which I did in Excel, will give you an idea of what I would consider to be a clear idea of how you would design the database. This example deals with a project database that keeps track of employees and the projects they’re working on. Notice how the following items are clear from this description:

The name of each table, with the Leszynski prefix.
The name of each field, with the Leszynski prefix. (The field name prefix will tell me what you’re proposing for the field type – Text, Currency, Date/Time, Number, etc.) (Remember that num is not the prefix we use for Number fields.)
The proposed primary and foreign key fields in each of the tables.
The type of relationship that will join each set of tables.

<font >

Hand in your table designs in either a Word or Excel document. Name the file <Your First Name> Week 4 Final DB. I do not want this handed in as an Access file.
I should warn you here: I really need you to name your files using this naming convention. If your files aren’t clearly named, I will keep bugging you about it (as some of you have witnessed with last week’s assignment).
Product Code Customer ID Rep ID Quantity Ordered Date of Order Date Filled Order Complete?
B12 1016 B02 300 2/5/2002 2/10/2002 Yes
DF14 1003 B02 250 4/7/2002 4/17/2002 Yes
DF16 1020 A02 175 3/12/2002 3/15/2002 Yes
DF210S 1019 B05 150 7/18/2002 7/21/2002 Yes
DF242 1005 A02 2,000 8/1/2002 8/10/2002 Yes
DF24S 1006 B02 380 5/16/2002 5/20/2002 Yes
DF28 1009 A14 400 10/1/2002 10/9/2002 Yes
O12 1010 C02 250 11/30/2002 12/4/2002 Yes
O38 1011 A05 1,800 1/3/2003
P12 1012 C02 1,750 1/10/2003
P14 1014 C09 1,200 6/19/2002 6/24/2002 Yes
P34 1003 B02 1,150 7/18/2002 7/21/2002 Yes
P34T 1012 C02 980 8/1/2002 8/10/2002 Yes
P38 1005 A02 650 8/20/2002 9/3/2002 Yes
P58 1001 C09 500 6/7/2002 6/17/2002 Yes
P58 1019 B05 975 1/20/2003
P58 1020 A02 600 2/5/2002 2/10/2002 Yes
SP16 1004 B05 1,100 4/7/2002 4/17/2002 Yes
SP210 1019 B05 1,265 12/10/2002 12/15/2002 Yes
SP212 1002 A02 1,100 1/23/2003
SP243 1015 A14 1,400 1/25/2003
P58 1007 A02 475 1/29/2003
SP26S 1005 A02 550 6/19/2002 6/24/2002 Yes
SP28S 1016 B02 880 7/1/2002 7/11/2002 Yes
W12 1010 C02 900 7/18/2002 7/21/2002 Yes
wp0113 1018 C11 400 8/1/2002 8/10/2002 Yes
wp0118 1019 B05 345 2/5/2002 2/10/2002 Yes
YP210 1005 A02 282 4/7/2002 4/17/2002 Yes
YP242 1020 A02 190 3/12/2002 3/15/2002 Yes
YP243 1009 A14 875 7/18/2002 7/21/2002 Yes
YP24R 1010 C02 740 5/17/2002 5/24/2002 Yes
YP24RG 1005 A02 550 1/29/2003

This will be the first of 8 individual assignments that you’ll be doing throughout the semester to create your own database. These assignments will be handed in and graded, as will the final version of the database.

This assignment is extremely important, because you need to make sure your database is designed correctly before you start actually creating it and populating it with data. So please spend an adequate amount of time on this.

NOTE: This is the first Final Database assignment, but it’s the second assignment of this semester. Therefore, the name that you will need to attach to the file will reflect the number of the week. See below for the precise naming method I would like you to use.

Here’s the assignment:

Read through the contents of the Word document named Final DB Project Description. (It’s located in the Class Data Files sub-folder, and it is also provided as the last lesson this week.) This file will provide you with all the information you need to start laying out your final database.
Based on the objectives described in the Final DB Project Description, plan out your database.

Decide how many tables you will need, and which fields will be in each one of the tables. Make absolutely sure your tables are fully normalized, so that there is no redundancy.
Specify the data type for all fields. Use the Leszynski Naming Convention for all table and field names.
Determine which relationships will need to be created, as well as the type of relationship. Clearly indicate the type (i.e., One-to-One, One-to-Many) for all proposed relationships. You should avoid Many-to-Many relationships for this database. That type of relationship falls outside the parameters of this class and isn’t necessary in this file.
Decide which fields will be the primary key fields and which ones will be the foreign key fields of the different proposed tables.
Indicate which fields will be connected to which fields in the relationships. Make sure it is clear to me when I look at your tables which fields will be connected to which other fields, as well as what you are proposing as the type of join.

The best way to do this would be by drawing lines to connect the fields. If you do this in Excel or Word, you can use the Line tool (available on the Drawing toolbar) or one of the AutoShapes (also on the Drawing toolbar), or you can simply write out a description of the different relationships. Regardless of your method, make sure I understand your objectives with regard to the different relationships that you’re proposing.

The following sample, which I did in Excel, will give you an idea of what I would consider to be a clear idea of how you would design the database. This example deals with a project database that keeps track of employees and the projects they’re working on. Notice how the following items are clear from this description:

The name of each table, with the Leszynski prefix.
The name of each field, with the Leszynski prefix. (The field name prefix will tell me what you’re proposing for the field type – Text, Currency, Date/Time, Number, etc.) (Remember that num is not the prefix we use for Number fields.)
The proposed primary and foreign key fields in each of the tables.
The type of relationship that will join each set of tables.

<font >

Hand in your table designs in either a Word or Excel document. Name the file <Your First Name> Week 4 Final DB. I do not want this handed in as an Access file.
I should warn you here: I really need you to name your files using this naming convention. If your files aren’t clearly named, I will keep bugging you about it (as some of you have witnessed with last week’s assignment).
Product Code Customer ID Rep ID Quantity Ordered Date of Order Date Filled Order Complete?
B12 1016 B02 300 2/5/2002 2/10/2002 Yes
DF14 1003 B02 250 4/7/2002 4/17/2002 Yes
DF16 1020 A02 175 3/12/2002 3/15/2002 Yes
DF210S 1019 B05 150 7/18/2002 7/21/2002 Yes
DF242 1005 A02 2,000 8/1/2002 8/10/2002 Yes
DF24S 1006 B02 380 5/16/2002 5/20/2002 Yes
DF28 1009 A14 400 10/1/2002 10/9/2002 Yes
O12 1010 C02 250 11/30/2002 12/4/2002 Yes
O38 1011 A05 1,800 1/3/2003
P12 1012 C02 1,750 1/10/2003
P14 1014 C09 1,200 6/19/2002 6/24/2002 Yes
P34 1003 B02 1,150 7/18/2002 7/21/2002 Yes
P34T 1012 C02 980 8/1/2002 8/10/2002 Yes
P38 1005 A02 650 8/20/2002 9/3/2002 Yes
P58 1001 C09 500 6/7/2002 6/17/2002 Yes
P58 1019 B05 975 1/20/2003
P58 1020 A02 600 2/5/2002 2/10/2002 Yes
SP16 1004 B05 1,100 4/7/2002 4/17/2002 Yes
SP210 1019 B05 1,265 12/10/2002 12/15/2002 Yes
SP212 1002 A02 1,100 1/23/2003
SP243 1015 A14 1,400 1/25/2003
P58 1007 A02 475 1/29/2003
SP26S 1005 A02 550 6/19/2002 6/24/2002 Yes
SP28S 1016 B02 880 7/1/2002 7/11/2002 Yes
W12 1010 C02 900 7/18/2002 7/21/2002 Yes
WP13 1018 C11 400 8/1/2002 8/10/2002 Yes
WP18 1019 B05 345 2/5/2002 2/10/2002 Yes
YP210 1005 A02 282 4/7/2002 4/17/2002 Yes
YP242 1020 A02 190 3/12/2002 3/15/2002 Yes
YP243 1009 A14 875 7/18/2002 7/21/2002 Yes
YP24R 1010 C02 740 5/17/2002 5/24/2002 Yes
YP24RG 1005 A02 550 1/29/2003