COMP 266 Study Guide - Midterm Guide: Functional Dependency, Data Definition Language, Data Integrity
1 Jul 2018
School
Department
Course
Professor
CMPT 354 Page 1 of 7
CMPT 354
Midterm Exam Key
Total marks: 72
Date: March 1, 2000
2000-1
Instructor: G. Louie
This exam is closed book. You have 1 hour and 15 minutes. No talking! You may
answer the questions in any order that you choose, but clearly indicate the question
number in your answer. Write clearly: if we can't read it, you get no marks. Good luck!
1. A company database needs to store information about employees (identified by
emp_id, with salary and phone_no as attributes); departments (identified by dept_no,
with dept_name and budget as attributes); and dependent children of employees (with
name and age as attributes).
Employees work in departments (i.e. an employee can work in more than one
department); each department is managed by an employee; a child must be identified
uniquely by name when the parent (who is an employee; you can assume that only
one parent works for the company) is known. We are not interested in information
about a child once the parent leaves the company.
a) (7 marks) Construct a clean and concise ER diagram using the Chen notation for this
database. Clearly indicate the cardinality mappings in the ER diagram.
A possible solution is presented here. Start w. 7 marks and deduct one
for each element in the diagram which is not consistent with the
requirements as set out above (i.e. missing entities, relationship sets, or
attributes, incorrect mapping cardinalities, lines missing arrowheads,
incorrect identification of weak entity set, etc.). Note that other
solutions are possible and as long as the requirements specified above are
met (
i.e.
entities, relationship sets, attributes & mapping cardinalities are
correct) and any reasonable assumptions are properly documented, full
marks should be given.
find more resources at oneclass.com
find more resources at oneclass.com
CMPT 354 Page 2 of 7
b) (15 marks) Give an SQL schema definition for the ER diagram from part a). Clearly
indicate any integrity constraints that should hold on this schema.
Deduct one mark for each incorrect/extraneous relation, incorrect or
incompatible domain, or incorrect identification of the primary key. Note
that the names of the relations for the relationship sets can be
different.
create table employees
( emp_id integer not null,
salary numeric(6,2),
phone_no char(10),
primary key (emp_id)) 2 marks
Employees
Emp_id
Salary
Phone_no
Manages Departments
dept_no
dept_name
Budget
Works_in
Dependent
Child
Name age
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
You may answer the questions in any order that you choose, but clearly indicate the question number in your answer. Write clearly: if we can"t read it, you get no marks. We are not interested in information about a child once the parent leaves the company. a) (7 marks) construct a clean and concise er diagram using the chen notation for this database. Clearly indicate the cardinality mappings in the er diagram. Note that other solutions are possible and as long as the requirements specified above are met (i. e. entities, relationship sets, attributes & mapping cardinalities are correct) and any reasonable assumptions are properly documented, full marks should be given. Name age b) (15 marks) give an sql schema definition for the er diagram from part a). Clearly indicate any integrity constraints that should hold on this schema. Deduct one mark for each incorrect/extraneous relation, incorrect or incompatible domain, or incorrect identification of the primary key.
