CIS 135 DESIGNING A RELATIONAL DATABASE TO STORE INFORMATION
ABOUT DVDs
A database is a collection of data kept in tables. A database that has two or more connected
tables is referred to as a relational database. Field: A table's columns that hold a certain type of
data (such as DVD Title or Genre Name). Rows in a table that include all the category details on
a single person, place, or item are called records (DVD Title). The type of values that users can
enter in the field is determined by the data type. With Access 2016, the following data formats
are available: Short Text, Long Text, Number, Data/Time, Currency, AutoNumber, Yes/No, OLE
Object, Hyperlink, Attachment, Calculated, and Lookup Wizard are some of the text formatting
options. Field Properties: You may modify the field properties in Design View to alter the kind of
data that can be inserted into your table. Field size, date and time formatting, decimal currency
values, input masks, and criteria for confirmation, comments, etcetera.
Lookup Wizard: Creates a lookup field with a drop-down menu of possible values for the user to
select from. You can input the values that appear in the value list or use another table or query to
provide the lookup values. A primary key is an identifier that is specific to each entry in a
database. A table's records are arranged alphabetically according to the main key field. A field in
a linked database (child table) that refers to the primary key in the first table is known as a
foreign key (parent table). It is necessary for the foreign key to have the same data type and field
size as the corresponding primary key, even if this is not a requirement. (The AutoNumber field
would be the lone exception.
Description, category, and duration of the DVD produced in, Actor, Main, Rating (You could add
others, such as the Date Released, and the Cost, which would provide more data for
calculations). Do you notice any informational categories that could be replicated over several
DVDs? There may be more than one adventure DVD in your genre collection. You could own
more than one DVD with the PG-13 rating.
Now let's organize the data into three distinct tables, tblDVDs, tblGenres, and tv Ratings, and
give each table a Primary Key Field. Each record's main key field must include specific
information. The records in the table will be alphabetized by the main key field when it is closed
and reopened. It is customary for the primary key to be the first field in a table, but this is not
required.
tblGenre
(Parent
Table)
tbIDVD
(Child
Table)
tblRating
(Parent
Table)
t
t
t
“cr
GenrelD
“a”
DVDID
“cr
RetINBID
GenreName
DVDTitle
RatingName
Genre
Description
Length
RatingDescription
YearProduced
MainActor
GenrelD
(Foreign
Key)
RatingID
(Foreign Key)
tblGenre
“GenrelD
GenreName
GenreDescription
ACT
Action
Contains
high-energy,
heart-pumping
scenes
ADV
Adventure
Based
on
a
journey
or
quest
witha
very
detailed
settings
COM
COMEDY
Contains
content
whose
main
purpose
is
to
make
people
laugh
CRI
CRIME
Focused
on
the
characters
either
breaking
or
enforcing
the
law
DRA
Drama
Contains
serious
plot
that
contains
real-life
characters
FAM
FAMILY
Provides
entertainment
for
the
entire
family
HOR
Horror
Contains
scenes
intended
to
frighten
or
thrill
audiences
SCI
Science
Fiction
Takes
viewers
into a
world
of
futuristic
technology
MUS
Musical
Focused
around
the
soundtrack
WES
Western
Centered
around
cowboys
and
the
Wild
West
tblRatings
a
RatingName
RatingDescription
G
General
Audiences
Suitable
for
all
ages
PG
Parental
Guidance
Suggested
Some
material
may
not
be
suitable
for
children
PG-13
Parents
Strongly
Cautioned
Some
material
may
be
inappropriate
for
children
under
13
R
Restricted
Under
17
requires
accompanying
parent
or
adult
guardian
x
Viewing
by
Adults
Excessive
violence
or
explicit
sexuality
When the columns are formed, you should construct the relationships by dragging from the
primary key in the parent table to the associated foreign key in the child table in the
Relationships pane. When you choose the Enforce Referential Integrity option, the user will be
prevented from adding a GenreID to the blVD database if one doesn't already exist in the tblGen
table or a RatingID if one doesn't already exist in the tblRatings column.
Table associations can be of three different types: One-to-One: Just one row from one table can
match one row from another related table. While it is typically as simple to put all the data in one
table, this is not a frequent connection. An example of a potential scenario would be a table
similar to Employee where the pay data is kept in a separate database. In this scenario, a
distinctive identity like EmployeeID or SS# would serve as the Primary Key in both tables.
One-to-Many: A row in Table A (tblGenre) could have several rows that match rows in Table B
(tblDVDs), while a row in Table B (tblDVDs) might only have one row that matches row in
Table A. (tblGenre).
