Overview

This assignment requires you to convert a UML class diagram into a relational model, physically create tables in Microsoft Access, and enter data into the tables. The main purpose of this assignment is to provide hands-on experience with the physical level of the database design process. Successful completion of this assignment will provide you with an enhanced understanding of Microsoft Access and relational databases. You will also increase your understanding of multiplicities.

As we have discussed in class, the first steps in designing a relational database include requirements analysis and conceptual modeling of the business processes, and then converting the conceptual model into a minimal set of relational database table structures. A benefit of conceptual modeling we discussed is that it is independent of any particular database system (such as Microsoft Access). At the logical level, a choice has been made as to the type of database system (relational database for this course) but it is still independent of particular software (i.e., Oracle, DB2, or some other relational database software could be used instead of Access). The physical level involves particular vendor database software that is used to create a new database, add tables to the database, specify relationships between those tables, and enter transaction data into those tables. The material covered in class, Chapter 3, and the Help facility in Microsoft Access provide guidance for you to perform the following required tasks (1 through 6) to complete this assignment. It is strongly advised that you finish one task before going on to the next. This assignment can be completed individually or in groups of two people. Each student must take responsibility for his or her learning related to the assignment.

Required Tasks

Attached you will find a conceptual level REA model for Lakers Sport Shop (LSS). This model is incomplete in order to keep the assignment manageable in a short timeframe but contains enough classes and associations to give you valuable practice at creating logical and physical level models. Please do NOT add any additional classes, associations, or attributes to the model. Also, I want to comment about academic integrity. You can discuss this assignment with other students currently enrolled in the class (but no one else). Furthermore, as stated in the syllabus, it is not acceptable to use a solution from a prior semester in any way. If you violate the academic integrity standards you will receive a zero and possibly other penalties.

1. Convert the conceptual model into a logical level model using the relational model (i.e., derive a set of relational tables on paper). Your database should not implement any foreign keys if there is a possibility that the foreign keys could contain null values (the minimum multiplicities provide related information).

2. Create a new database in Microsoft Access that has your last and first name(s) as the name of the database (e.g., "Adler Amy for an assignment submitted by Amy Adler; teams should list all team member names alphabetically by last name). As you know, it is always a good idea to make backups of your work. Important note: If you do not create a new database, you will receive a zero for this assignment.

3. Add each of the tables from your relational solution for LSS (see task 1 above) to the database, defining the data types for each field and identifying the primary key(s) for each table. DO NOT choose the Large Number data type for any field. For foreign keys, enter foreign key from ________table (filling in the name of the table from which it was posted) into the fields description field. This is not required by Access but is an assignment requirement to verify your understanding of the foreign keys (and it is a good habit for documentation purposes). For each foreign key field, if the related minimum multiplicity requires mandatory participation, make sure that the field requirement is "yes" (not "no" which is the default). For example, say you have a Sale 0..* 1..1 Salesperson association implemented with SalespersonID posted into the Sale table (note that these are different tables that the assignment tables). SalespersonID is a foreign key in the Sale table, and the participation of Sale in its association with Salesperson is mandatory. That is, we cannot enter data about a sale without specifying which salesperson was responsible for that sale. In that case, SalespersonID must be set for required data entry = yes.

4. Create and save the relationship layout for the database, including ALL tables and relationships in the database (this should be done after you have entered the tables from task 3 above). Set referential integrity for all relationships.

5. Make up data to enter into the database tables. Each students or teams data must be different (i.e., if you help each other on this assignment, but you are not a team, you must create different data for each database that gets turned in). Each table must have at least 3 records (rows) and you must enter data that is internally consistent and that conforms to the minimum and maximum multiplicities. For internal consistency, you need to make sure the data makes sense when viewed as a whole. For example, if you indicate that cash receipt #478 was applied to sale #1764, and sale #1764 was made to customer #2, then cash receipt #478 should also be indicated as received from customer #2.

For the minimum multiplicities, if there is optional participation (for example, a salesperson can be entered into the database before any related transactions occur) you need to have data reflect this (e.g., a row for a salesperson in the "Salesperson" table that is not included in any of the rows in the Sale table in other words, create a salesperson that hasnt yet made a sale). If participation is mandatory (such as: a sale has to have a customer) you need to have data reflect this (every sale record has a customer associated with it). If you followed the instructions in task 3 above and specified such fields as required, Access will force you to enter a data value for those mandatory cases that involved foreign keys. However, for mandatory participation in associations that are implemented with separate tables (e.g., association class tables), you will need to enter the data appropriately and will get no warning from Access.

For the maximum multiplicities, your data should illustrate either "at most one" or "at most many" participation. For example, say there is an association between sale and inventory such that one invoice can include multiple inventory stock numbers and one inventory stock number may be included on multiple sale invoices. Your data should reflect at least one sale that includes multiple inventory items, and at least one inventory item number that is sold more than once. Note that in some cases conforming to the maximum multiplicities may require you to enter more than 3 records in a table. When making up data values keep it simple using a data value like 10 for an employee number is preferred over a value like 74088241749.

6. Before you upload your file, review the questions at the bottom of page 3. Turn in the Microsoft Access database file by October 17, 2020 by 11:59pm EST. Upload the Access file (one Access file per group please) into the Assignment area in Blackboard. Important note: you should close the Access application before you upload the Access file to Blackboard.

Other information:

LSS eventually pays for nearly all (99%) of its purchases; only very rarely does LSS return merchandise to a supplier.

LSS hires a payroll specialty firm (WKP, which stands for We Know Payroll) to handle writing paychecks to employees. Thus, LSS writes one check per month to WKP to cover the employees gross pay plus WKPs processing fees. Occasionally LSS also writes checks for other things such as loan re-payments and general and administrative service acquisitions, which are part of other conceptual models / views; however, most of its cash disbursements (approximately 90 out of every 100 checks) apply to purchases of inventory from suppliers. Your data should contain an example of a particular cash disbursement that would relate to one of these other views (and therefore would not have an associated Supplier ID because the other views would have other external agents).

Questions you should ask yourself before turning in the assignment:

1. (Tables) How many tables do I have? Given the UML class diagram (including multiplicities), does this number make sense? Have all associations (from the diagram above) been implemented (either as tables or foreign keys)? Does every table have a designated primary key? Are all foreign keys documented (e.g., foreign key from supplier table)? For foreign keys only, did I make required fields=yes as appropriate? Do the data types for my attributes make sense?
2. (Relationship Layout) Did I add all tables to the relationship layout? Did I connect every foreign key to its related primary key? Did I connect every composite key to the primary keys used to create it? Did I enforce referential integrity on all relationships?
3. (Data) Does every table have at least three rows of data? Did I go through each multiplicity and make sure that I have examples of the multiplicities illustrated in my data? (This part usually gives students the most trouble, so make sure you double-check.)