1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
|
# Project 3: Data Modeling and Normalization
## Overview
The task in this project is to design a normalized database that could be used
to collect and store information for a video rental outlet. The database should
be structured to support tasks such as:
* tracking rentals by title and by customer;
* producing reports, such as rentals due on a certain date;
* categorizing rentals by media type (e.g., Blu-Ray, DVD, Game).
The assignment does not require you to produce examples of the reports listed,
but only that the database be correctly structured to allow the query and
reporting features of a DBMS, such as Access, to work properly.
You will need to collect information about several titles in each category,
which can be done online, at your local video store, or from your personal media
collection. You will be given an initial, un-normalized field list of data items
to be tracked, which you will normalize into the appropriate separate tables.
The database students will prepare in this project is somewhat simplified in
comparison to a real-world application. For example, only brief customer address
information is included, to reduce the amount of data input required for the
project. We have also simplified the example in terms of structure; ignoring,
for example, the complexities of a real-world video outlet, such as varying
rental periods for different products.
The initial field list does not include all of the appropriate key fields for
the tables that will be created, so suitable key fields should be added to the
tables as required.
This project is most easily completed using MS-Access; for a detailed discussion
of the other options for completing the Project, please see "Alternatives to MS
Access" below.
## Project Details
The following is a list of essential data items to be recorded. As noted above,
additional fields may be required as keys for the tables you will create from
these fields.
* date of rental
* due date
* media item title
* media item category
* category description
* category code
* customer ID#
* customer lastname
* customer firstname
* customer address
If only a single table were made from all the fields listed above, the result
would be a system in which every rental would have to include duplicate customer
and media item details. In addition to wasting input time duplicating data,
such a system would also be highly prone to error, because any change to
customer data (a change of address, for example) would also result in different
records showing different content for the same fields, unless every historical
record for that customer was updated.
The relational database model was designed to solve those problems by
identifying key data entities, separating them into their own tables, and
relating the tables using foreign keys. (The foreign key is usually the primary
key from one table, placed in one or more other tables to create linkages.)
This allows for customer details, for example, to be recorded once, in a table
reserved for that purpose, and referenced in other tables by a unique identifier
(key), such as Customer ID#.
Your task is to create a simple relational database from the field list above.
The required tables are Customers, Media, Categories, and Rentals; each of which
should be properly provided with a primary key, and each of which should be
related to other tables as required.
All four database tables should be populated with records as follows:
* Customers — at least 10
* Categories — three records, one describing each of the media types: DVD, Blu-Ray, or Game
* Media — at least 30 records, representing a selection of DVD movies, Blu-Ray movies, and Games.
* Rentals — at least 20 records.
## Marking Criteria
This project represents 15% of your final grade, and will be graded according
to the following criteria:
DATABASE NORMALIZATION TABLES (50 marks)
* There should be four tables, with the correct fields.
* For each missing or incorrectly placed field, 10 marks will be deducted.
KEY FIELDS (40 marks)
* Key fields have been correctly assigned to each table.
* For each missing or incorrect key field, 10 marks will be deducted.
OTHER FEATURES (10 marks)
Use of a Query, Use of Input Form, OR Report, OR use of Field Attributes
(i.e., Input Mask, Caption, or Default Value, for projects completed using
MS-Access), OR provision of annotated SQL statements (as described below under
ALTERNATIVES TO USING MS-ACCESS).
## Using MS-Access
You now have the option to download and install MS-Access (free to SCIS
students).
For help with the basics of MS-Access, refer to the tutorials and step-by-step
directions for basic operations and functions that are included in the Help
features of your MS Access software. Search "getting started" in the help
utility, and look at the sections on Access basics, creating tables, and
producing queries. This approach will provide help that is specific to your
particular version of the software. If you have one available, you may also
want to refer to a general MS Office desk reference, and review the sections
pertaining to basic Access operations.
## Alternatives to MS-Access
To complete the project without using MS-Access or another DBMS, you will need
to create the correctly normalized tables in some other application (e.g., Excel
or Word), including the required fields, and with the appropriate key fields
clearly identified. The tables should be populated with data, as required and
described elsewhere in the project instructions.
In place of applying the extra DBMS functions (forms, queries, or field
attributes) listed as part of the project requirements, students pursuing this
approach will submit three SQL statements; each documented or annotated to
describe the expected query results. The most basic approach to this requirement
would be to produce three variations of the SELECT statement, each designed to
produce a different list of rentals according to specified criteria. The
criteria could relate to a customer, a specific media category, a title (for
example, all rentals for a certain date, title, or customer), or some other
aspect of the data.
A general description of SQL may be found by checking the index listings for SQL
in your textbook, and reviewing the pages listed.
|
