Module: Database Essential II
Final Exam
Total marks: 100
Exam Duration: 3 hours
Special Instructions:
• Take a screen shot of the answers that are completed using MS Access and add the screen
shots of the answer under the respective question.
• Two databases (Li
ary and Sales) are provided. Download them to answer the respective
questions.
• Write down your name at the top of the answer sheet.
• Missing Name and Late submissions will have penalty of 5 marks each.
• Any sort of copying is strictly prohibited. If copying is detected, then your exam will
e cancelled, and you will get 0 for this exam.
Question 1 (12 marks): Create a new MS Access database called “Books”. Create three tables
Book, Author and Loan using the DDL command “CREATE” in MS Access one by one. The tables
will have following attributes and information:
Table Name Attribute Name Attribute
Type
Size
Book bookId (set as Primary key)
title
Text
Text
50
100
Author authorId (set as Primary key)
lastName
firstName
gender
Counter
Text
Text
Text
50
50
6
Loan authorId
ookId
dateDue
eturned
Note:
Primary Key: (authorId, bookId, dateDue)
Foreign Key: authorId
Foreign Key: bookId
Integer
Text
DateTime
YesNo
50
Now answer the following questions:
1.a) Write down the DDL commands you used to create the above three tables.
1.b) Give screen shot of the relationship diagram from the relationships tool of MS Access after
unning the DDL commands for the above table creation.
1.c) Write down the DDL command to remove all the tables from the “Books” database.
Question 2 (9 marks): Use the Li
ary Database to answer the following. Show your works
using the screen shot from MS Access.
2.a) List the titles of books where the author name ends with “Celko” and the text “data” appears
in the title.
2.b) List titles of books where the title contains the word “medieval”
2.c) List the titles of books where the title contains the words “medicine” and “medieval”.
Question 3 (12 marks): Use the Tables (Book, Loan and Member) from the Li
ary Database
to write down the SQL queries for the following:
3.a) For each loan show the title of the book and the date it was bo
owed.
3.b) Produce a list that shows for each loan the book title, the name of the member who
o
owed the book, and the dates the book was bo
owed and then returned.
3.c) For member id 2, list the person`s name and the titles bo
owed.
3.d) Produce a list of book titles and member names for those books that have not been
eturned. (Hint: In this case you must give the criteria for dateReturned as null.)
Question 4 (20 marks): For this question, create a database called “University”. Then create the
following two tables Course and Department. The first few rows of Course and Department are:
Note that these two tables have the deptCode field in common.
- In Department table, deptCode is the primary key and is used to identify a specific department.
- In Course table, the deptCode field is a part of the primary key and indicates the department to
which the course belongs.
Now, write queries to the following question and show your screen shots from MS Access:
4.a) List all courses in Mathematics and English where the credit hours are greater than 1.
4.b) List all 3 and 6 credit hour courses that are not ACS courses.
4.c) List courses where the course description contains the word computer but where the course is
not offered by the Applied Computer Science department.
4.d) Create a query to list the department codes (with no duplicates) of departments that offer 6
credit hour courses.
4.e) For each department list the department code, the largest value for credit hours, and the
number of courses.
Question 5 (6 marks): Consider the two tables A and B below.
Table A Table B
5.a) How many rows are in the result if A and B are joined based on the attribute X?
5.b) How many rows are in the result if A and B are joined based on both attributes X and Y?
Question 6 (8 marks): Consider the following SQL statements for the Sales database, and show
how each statement would appear in Design View. You can confirm your result if you create a
query, switch to SQL View, type the query statement and then switch to Design View. Add your
screen shot as the answer for each question.
6.a)
6.b)
Question 7 (9 marks):
7.a) Define the following three terms: Weak Entities, Relationships and Composite Attributes
7.b) Consider your place of work. The Human Resources department in your company needs to
manage information about its employees.
- How many employee entities are there?
- What attributes describe these entities?
7.c) Consider your educational institution.
- What are some of the entity types that would be useful?
- What relationships exist that relate entity types to one another?
- What attributes would be useful to describe entities and relationships?
Question 8 (9 marks):
8.a) A college or university will keep track of several addresses for a student, but each of these
can be named differently: for example, consider that a student has a mailing address and a home
address. Create an ERD for a student entity type with two composite attributes for student
addresses where each comprises several single-valued attributes.
8.b) Consider a li
ary application that needs to keep track of books that have been bo
owed.
Suppose there is an entity type Loan that has attributes bookID, memberID, dateBo
owed and
dateDue. Suppose the due date is always 2 weeks after the bo
owed date. Show date due as a
derived attribute (computed from date bo
owed).
8.c) Map the following ERD to relations.
Question 9 (15 marks):
9.a) Consider a company that owns and operates parking lots. Draw an ER Diagram with two
entity types Parking Lot and Space and where:
- The address of a parking lot serves to identify the lot.
- Each space within a lot is rented at the same monthly rental charge.
- Each parking space is known by its number within the lot (within a lot these always start at 1).
- Each parking space is rented out to at most one vehicle. The vehicle’s identifier must be
ecorded. The identifier comprises a province code and license plate number.
9.b) Develop an ERD to keep track of information for an educational institution. Assume each
course is taught by one instructor, and an instructor could teach several courses. For each instructor
suppose we have a unique identifier, a first name, a last name, and a gender. Each course belongs
to exactly one department. Within a department courses are identified by a course number.
Departments are identified by a department code.
9.c) Develop an ERD to support the management of credit cards. Each credit card has a unique
number and has a customer associated with it. A customer may have several credit cards. The
customer has a first name, last name, and an address. Each time a customer uses a credit card we
must record the time, the date, the vendor, and the amount of money involved.