Assignment 1 - Developing a Database Application
Question # 00411850
Posted By:
Updated on: 10/23/2016 06:34 AM Due on: 10/23/2016
ITIS 2P91 (D2) FALL 2016 Assignment 1: Developing a Database Application
(40 points and 7.5% of Final Grade)
Due date: October 23, 2016, by 11:55PM via Sakai
This is a group assignment. For this assignment you will be responsible to creating a
database, forms, queries and reports. You are required to create (1) a database based on the
narration and the Entity Relation Diagram (ERD) depicted below, (2) forms that facilitate
data entry, and (3) queries and reports that satisfy the stated requirements. Accuracy,
feature, and function of the resulting database, queries, forms, and reports are used as the
criteria to evaluate your submission. You may add features on the entities and make assumptions,
if there is any need to do so. If you do so, you are required to write down all of your assumptions as well
as the features you added as a note when you submit the final work.
There are lots of materials on Database Management in general, and on MS Access
specifically on the Internet that help you complete the required tasks. Here are links to a
tutorial and an ebook, for example, MS-Access Access 2013 videos and tutorials, Microsoft
Access 2013 Step by Step ebook.
SUBMISSION: October 23, 2016, by 11:55PM via Sakai
Submission should be done before the deadline using the Assignment 1 link on Sakai. You
are required to submit one MS Access database file per group through the course website.
Groups must be between 4 and six students. Choose a team member for uploading the files.
Use the following convention to name the files that you submit (GroupName_Database).
You can have any name for the group.
Verify that your assignment file is submitted properly through the course website. Late
submissions and wrong files will automatically receive a grade of zero.
Read the following case study, which describes the data requirements for a DVD rental
company. The DVD rental company has several branches throughout Canada. The data
held on each branch is the branch address made up of street name, street number, city,
province code, and postal code, and the telephone number. Each branch is given a branch
number, which is unique throughout the company. Each branch is allocated staff, which
includes a Manager. The Manager is responsible for the day-to-day running of a given
branch. The data held on a member of staff is his or her name, position, and salary. Each
member of staff is given a staff number, which is unique throughout the company. Each
branch has a stock of DVDs. The data held on a DVD is the catalog number, DVD number,
title, category, daily rental, status, and the names of the main actors, and the director. The
catalog number uniquely identifies each DVD. However, in most cases, there are several
Page 1 of 4 ITIS 2P91 (D2) FALL 2016 copies of each DVD at a branch, and the individual copies are identified using the DVD
number. A DVD is given a category such as Action, Adult, Children, Drama, Horror, or
Sci-Fi. The status indicates whether a specific copy of a DVD is available for rent. Before
hiring a DVD from the company, a customer must first register as a member of a local
branch. The data held on a member is the first and last name, address, and the date that the
member registered at a branch. Each member is given a member number, which is unique
throughout all branches of the company. Once registered, a member is free to rent DVDs,
up to maximum of ten at any one time. The data held on each DVD rented is the rental
number, the full name and number of the member, the DVD number, title, and daily rental,
and the dates the DVD is rented out and date returned. The rental number is unique
throughout the company. Refer to the ERD diagram below for further information on the
entities and relationships.
1. Using Microsoft Access, create all of the required tables. Choose appropriate datatype
and size for each of the attributes. (10 marks)
2. Create the appropriate relationships between tables. (3 Marks)
3. Populate the BRANCH table with at least three records. For the first two branches,
create at least : (Hint: Before populating CHILD tables with data, populate PARENT tables) (5
Marks) 3 staff
10 Actors
10 DVD titles (each DVD has to have at least 2 Main Actors)
15 Videos for rent
10 Members (including your instructor and each member of your team) 4. Create forms to simplify data entry into each of the tables. (See Access Help for directions
on creating a form with a subform.) (5 marks)
5. Create at least one RENTS for each MEMBER. Include more than one DVD on at least
half of these RENTS. (2 Marks)
6. Create Queries that to do the following: (12 marks)
6.1. Count the number of Members in each Branch in each Region. Include the
following detail about each branch in the result: Province Name, Branch ID, Branch
Name and City, and arrange the result in an ascending alphabetical order by Region
Name. Here is a sample result: 6.2. List details of all of the Branch Managers. Sample result Page 2 of 4 ITIS 2P91 (D2) FALL 2016 6.3. List the details of all of the employees of the DVD rental company in Ontario. Arrange the
result in an ascending alphabetical order. 6.4. List the details of all Action DVD videos, arrange the result in an ascending alphabetical
order by title. 6.5. List the details of all the DVD action movies where Angelina Jolie is the Main Actor.
6.6. List the details of all the DVD movies that are currently on rent.
6.7. List the details of DVD movies that are overdue by each branch in each province.
6.8. List the details of Managers that are making more than 100,000 a year.
6.9. List the details of members who didn’t return videos in time; those members who
have outstanding overdue.
6.10.
List of Main Actors who passed away in 2016.
7. Create Reports to do the following: (3 marks)
7.1. That shows the number of Members that have rented more than three videos.
7.2. That shows the revenue generated from DVD rent by each branch in each province.
7.3. That shows branches in each province that have generated the highest revenue. Page 3 of 4 ITIS 2P91 (D2) FALL 2016
BRANCH MEMBER BranchID MemberID
MemFirstName
MemLastName
MemStreetNumber
MemStreetName
MemPostCode
MemEmailAddress
MembershipDate
BranchID (FK) ters BranchName
StreetName
StreetNumber
City
Province
PostCode
Z
TelephoneNum
StaffID (FK) STAFF
StaffID Has FirstName
MiddleNameInitial
LastName
Position
Salary
BranchID (FK) Manages Is_Allocated
Listed
VideoForRent 10
RENTS
RentID
DateofRent
P
ReturnDate
MemberID (FK)
DVDNumber (FK) DVD DVDNumber
tered DateAcquired
BranchID (FK)
DVDCatalogNumber (FK)
DailyRentalCost
Status DVDCatalogNumber
Has_copies Title
Category
Director
ReleaseDate
Has
P ACTOR Legend:
P - refers to one or more
Z - refers to Zero or one D VDMainACTORS ActorID
ActFistName
ActMiddleName
ActLastname
DateofBirth
DateofDeath ActorID (FK)
DVDCatalogNumber (FK) Is_Listed
P Page 4 of 4
(40 points and 7.5% of Final Grade)
Due date: October 23, 2016, by 11:55PM via Sakai
This is a group assignment. For this assignment you will be responsible to creating a
database, forms, queries and reports. You are required to create (1) a database based on the
narration and the Entity Relation Diagram (ERD) depicted below, (2) forms that facilitate
data entry, and (3) queries and reports that satisfy the stated requirements. Accuracy,
feature, and function of the resulting database, queries, forms, and reports are used as the
criteria to evaluate your submission. You may add features on the entities and make assumptions,
if there is any need to do so. If you do so, you are required to write down all of your assumptions as well
as the features you added as a note when you submit the final work.
There are lots of materials on Database Management in general, and on MS Access
specifically on the Internet that help you complete the required tasks. Here are links to a
tutorial and an ebook, for example, MS-Access Access 2013 videos and tutorials, Microsoft
Access 2013 Step by Step ebook.
SUBMISSION: October 23, 2016, by 11:55PM via Sakai
Submission should be done before the deadline using the Assignment 1 link on Sakai. You
are required to submit one MS Access database file per group through the course website.
Groups must be between 4 and six students. Choose a team member for uploading the files.
Use the following convention to name the files that you submit (GroupName_Database).
You can have any name for the group.
Verify that your assignment file is submitted properly through the course website. Late
submissions and wrong files will automatically receive a grade of zero.
Read the following case study, which describes the data requirements for a DVD rental
company. The DVD rental company has several branches throughout Canada. The data
held on each branch is the branch address made up of street name, street number, city,
province code, and postal code, and the telephone number. Each branch is given a branch
number, which is unique throughout the company. Each branch is allocated staff, which
includes a Manager. The Manager is responsible for the day-to-day running of a given
branch. The data held on a member of staff is his or her name, position, and salary. Each
member of staff is given a staff number, which is unique throughout the company. Each
branch has a stock of DVDs. The data held on a DVD is the catalog number, DVD number,
title, category, daily rental, status, and the names of the main actors, and the director. The
catalog number uniquely identifies each DVD. However, in most cases, there are several
Page 1 of 4 ITIS 2P91 (D2) FALL 2016 copies of each DVD at a branch, and the individual copies are identified using the DVD
number. A DVD is given a category such as Action, Adult, Children, Drama, Horror, or
Sci-Fi. The status indicates whether a specific copy of a DVD is available for rent. Before
hiring a DVD from the company, a customer must first register as a member of a local
branch. The data held on a member is the first and last name, address, and the date that the
member registered at a branch. Each member is given a member number, which is unique
throughout all branches of the company. Once registered, a member is free to rent DVDs,
up to maximum of ten at any one time. The data held on each DVD rented is the rental
number, the full name and number of the member, the DVD number, title, and daily rental,
and the dates the DVD is rented out and date returned. The rental number is unique
throughout the company. Refer to the ERD diagram below for further information on the
entities and relationships.
1. Using Microsoft Access, create all of the required tables. Choose appropriate datatype
and size for each of the attributes. (10 marks)
2. Create the appropriate relationships between tables. (3 Marks)
3. Populate the BRANCH table with at least three records. For the first two branches,
create at least : (Hint: Before populating CHILD tables with data, populate PARENT tables) (5
Marks) 3 staff
10 Actors
10 DVD titles (each DVD has to have at least 2 Main Actors)
15 Videos for rent
10 Members (including your instructor and each member of your team) 4. Create forms to simplify data entry into each of the tables. (See Access Help for directions
on creating a form with a subform.) (5 marks)
5. Create at least one RENTS for each MEMBER. Include more than one DVD on at least
half of these RENTS. (2 Marks)
6. Create Queries that to do the following: (12 marks)
6.1. Count the number of Members in each Branch in each Region. Include the
following detail about each branch in the result: Province Name, Branch ID, Branch
Name and City, and arrange the result in an ascending alphabetical order by Region
Name. Here is a sample result: 6.2. List details of all of the Branch Managers. Sample result Page 2 of 4 ITIS 2P91 (D2) FALL 2016 6.3. List the details of all of the employees of the DVD rental company in Ontario. Arrange the
result in an ascending alphabetical order. 6.4. List the details of all Action DVD videos, arrange the result in an ascending alphabetical
order by title. 6.5. List the details of all the DVD action movies where Angelina Jolie is the Main Actor.
6.6. List the details of all the DVD movies that are currently on rent.
6.7. List the details of DVD movies that are overdue by each branch in each province.
6.8. List the details of Managers that are making more than 100,000 a year.
6.9. List the details of members who didn’t return videos in time; those members who
have outstanding overdue.
6.10.
List of Main Actors who passed away in 2016.
7. Create Reports to do the following: (3 marks)
7.1. That shows the number of Members that have rented more than three videos.
7.2. That shows the revenue generated from DVD rent by each branch in each province.
7.3. That shows branches in each province that have generated the highest revenue. Page 3 of 4 ITIS 2P91 (D2) FALL 2016
BRANCH MEMBER BranchID MemberID
MemFirstName
MemLastName
MemStreetNumber
MemStreetName
MemPostCode
MemEmailAddress
MembershipDate
BranchID (FK) ters BranchName
StreetName
StreetNumber
City
Province
PostCode
Z
TelephoneNum
StaffID (FK) STAFF
StaffID Has FirstName
MiddleNameInitial
LastName
Position
Salary
BranchID (FK) Manages Is_Allocated
Listed
VideoForRent 10
RENTS
RentID
DateofRent
P
ReturnDate
MemberID (FK)
DVDNumber (FK) DVD DVDNumber
tered DateAcquired
BranchID (FK)
DVDCatalogNumber (FK)
DailyRentalCost
Status DVDCatalogNumber
Has_copies Title
Category
Director
ReleaseDate
Has
P ACTOR Legend:
P - refers to one or more
Z - refers to Zero or one D VDMainACTORS ActorID
ActFistName
ActMiddleName
ActLastname
DateofBirth
DateofDeath ActorID (FK)
DVDCatalogNumber (FK) Is_Listed
P Page 4 of 4
-
Rating:
/5
Solution: Assignment 1 - Developing a Database Application