Assignment

Overview

This assignment gets you started thinking like a database designer. You will learn concepts from chapters 1 through 5 and and implement them using MS Access.

You should complete the following activities:

1) See the submission requirements.

Submission Requirements

Part 1-

When completed, please upload your Word document to the "Assignment #1" area under the "Assignments" tab. In the event that Scholar isn't working, please send an e-mail to the course DLI with your Word Document as an attachment. Please use a document naming convention that allows the DLI to determine your name. EXAMPLE: "LastName_FirstName_Assn1.docx".

1) Create a sample relation (in Excel) with five columns and five tuples (rows). Create a primary key by highlighting the column. Create a second table with five columns one of which is a foreign key to the first table and five tuples (rows). Link the primary and foreign key values. Note any assumptions. (Use appendix #1 below as the basis of the expectation of the correct answer)

2) Using Chen notation, create a basic Chen diagram for the following cardinalities: 1:1, 1:M, and M:N. Create a situation that would be described by that diagram. Note any assumptions. (Use appendix #2 below as the basis of the expectation of the correct answer)

3) Create two sample functional dependencies, one to demonstrate a single attribute primary key and one to demonstrate a multi-attribute composite key. Also, summarize (in a sentence or two) the role and purpose of a foreign key and referential integrity. Note any assumptions.

4) For each normal form (1NF, 2NF, 3NF, BCNF, and 4NF), describe in a sentence the conditions that must be true in order for that normal form to be meet.

5) For each normal form (1NF, 2NF, 3NF, BCNF, and 4NF), create an example of conversion to that normal form. (For example, you would show the conversion from an un-normalized to 1st normal form, 1st to 2nd, 2nd to 3rd normal form, etc. (Note (1): If you are having a difficult time creating a relation example that can be converted from 1st all the way through to 4th normal form, you can create multiple examples consisting of different relations to demonstrate conversion between different normal forms (i.e. - a fresh set of relations for each normal form conversion, as it can be difficult to create an example that perfectly violates each normal form in order).

(Use appendix #3 as reference for this question)

Part 2-

This part of the assignment involves building an application using Microsoft Access that implements the following database for a fictitious credit card company, MasterVisa. Create a database and populate the database minimally with 10 records per table (you can add more, but keep within reason as this assignment is not a data entry exercise).

Note: please use tasteful names.

When completed, please upload your MS Access database to the "Assignment #1" area under the "Assignments" tab. You may want to zip your database if your file becomes too large. In the event that Canvas isn't working, please send an e-mail to the course DLI with your Access Document as an attachment. If you choose to email your Access file, please zip the Access file and send it via email as a zip file. Many outlook clients remove MS Access files. Please use a document naming convention that allows the DLI to determine your name. EXAMPLE: "LastName_FirstName_Assn1.mdb"

Background Information

This database will require the following entities (shown below). The primary key fields are denoted in bold letters, and the foreign keys are denoted in italics and underlined.

Account(ANO, ANAME, STREET, CITY, STATE, ZIP)
Transactions(TID, DATE*, AMOUNT, STOREID, ANO)
Store(STOREID, SNAME, STREET, CITY, STATE, ZIP)

One account can have many transactions and one store can have many transactions. No additional normalization is required.

*I am open to how you interpret the date attribute. For example, you could have three separate columns (ex -- one each for month, day, and year) or a single column (ex - 1/1/08). Please refer to requirements #11 and #12 when making your decision. Also, MS Access may consider DATE a reserved word, so you can change this column name if need be.

6) Create an example E/R Diagram for the fictitious business information presented above. Note any assumptions. (Use appendix #4 (below) as the basis of the expectation of the correct answer, but you should ultimately create your own unique diagram.

7) Convert the E/R model (created in question #6) to a relational model. Note any assumptions. (Use appendix #4, #5 and #6 (below) as the basis of the expectation of the correct answer.)

8) Create a main interface that allows the user to execute the events listed below

(Hint: Create buttons on the main interface for each of the assignment requirements 9 through 14. User input can come in many different ways, including user prompts, forms with drop down menus, command buttons, etc. Ultimately, the choice is yours.).

(This interface should automatically initiate when the database is opened. See the Access lecture for instructions).

This main interface should allow the users to execute the following actions:

9) Create a form or forms that will allow users to maintain the tables, so that they can add or edit existing records (but not necessarily delete the record).

10) Create a report that lists all accounts, include any other attributes that you feel are relevant (no user input required).

11) Create a report of all transactions for a given day of the year (day, month, and year), include any other attributes that you feel are relevant (user input required - Date Attributes).

12) Create a report that will serve as the monthly statement for a given individual account that lists the date, Sname, and the amount of the transaction for that account. The user must specify the month. At the end of the statement, the total amount should be summed. (user input required - Account Number/Month/Year).

13) Create a report that lists all the transactions for a given account number, include any other attributes that you feel are relevant (user input required - Account Number).

14) Create an action query, other than the SELECT query (i.e. - crosstab, make table, update, append, delete query). Create a command button (clearly labeled) that initiates the action of this query. The query may or may not require user input.

The file with Appendices is also included in this folder.

Attachment:- Appendices.rar