database

IT 240 Name:

HW 1

Worth 10 points

Submit:

1. This updated Word Document with your name at the top

2. The school.accdb data base that you created in Part 2

Part 1

An example of normalizing data using a Movie Rental Service is:

Movie Rental Database

Last Name

First Name

Street

City

State

Zip

Movies Rented

Genre

Fee

Start(date/time -- start date of rental )

End(date/time – ending date of rental)

Normalized Tables:

MEMBER(MembershipID, LastName, FirstName, Street, City, State, Zip)

MOVIE(MovieID, MovieName, Genre, RentalFee)

RENTAL(TranscationID , MembershipID, MovieID, Start, End, AmountPaid)

Contraints:

MembershipID in RENTAL must exist in MEMBER

MoveID in RENTAL must exist in MOVIE

Types:

You must also specify types.

You may do so using a tale as shown below.

The second row contains the field names along with their data type and if it is a key.

MEMBER	MOVIE	RENTAL
MembershipID (int) LastName (text) FirstName (text) Street (text) City (text) State (text) Zip (text)	MovieID (int) MovieName (text) Genre (text) RentalFee (currency)	TranscationID (int) MembershipID (int) MovieID (int) Start (date/time) End (date/time) AmountPaid (currency)

Given the following data list, normalize the data and place the normalized tables in the Normalized Table below.

School

Departments

DepartmentName

Students

StudentName

Department they are in

DateOfBirth

Subjects

SubjectName

Grades

Grade a student receives in a specific subject (an integer 5 indicating an ‘A’, 4 a ‘B’, 3 a ‘C’, 2 a ‘D’, and 1 an ‘F’)

Faculty

FirstName

Last Name

Department

Degree

Place your normalized tables and specifications below:

Part 2

Create an *instance of your DB design in Access.

This will require you to do the following:

a) Create the DB and name it school.accdb

b) Create all the tables you designed above.

c) Make certain that the data types of the fields are correct

d) Populate your tables with data you make up

You must have a minimum of:

-- 4 departments (English, Computer Science, ….)

-- 4 students

--- 3 faculty

--- 4 subjects

--- all students need a grade in the subjects they took

* A database instance is the data stored in a database at a specific moment in time. It is a snapshot of the data at that moment in time.