From dd4fcba6d1d54773b0c26bb094a2c01057429135 Mon Sep 17 00:00:00 2001 From: mo khan Date: Mon, 30 Dec 2019 15:31:30 -0700 Subject: Add assignment 1 --- assignments/01.md | 129 ++++++++++++++++++++++++++++++++++++++++++++ assignments/Assignment1.pdf | Bin 0 -> 85095 bytes 2 files changed, 129 insertions(+) create mode 100644 assignments/01.md create mode 100644 assignments/Assignment1.pdf diff --git a/assignments/01.md b/assignments/01.md new file mode 100644 index 0000000..aedd06c --- /dev/null +++ b/assignments/01.md @@ -0,0 +1,129 @@ +# COMPUTER SCIENCE 378: INTRODUCTION TO DATABASE MANAGEMENT +## Assignment 1 + +Total: 100 marks +Weight: 10% of your final grade + +Due: After you have completed Unit 3. + +Answer all of the following questions, and upload your completed work here. + +### Question 1 (15 marks) + +Answer the following questions (250 words max/question). +* Explain the concept of program-data independence, and explain how it is achieved in the database approach. +* Briefly contrast and compare the following development approaches: the systems development life cycle and the prototyping methodology. +* Discuss the difference between entity type and entity instance. + +### Question 2 (15 marks) +Give a simple example of an E-R diagram for each of the following concepts: + +a. Associative entity +b. Weak entity +c. Unary relationship + +### Question 3 (20 marks) + +As an independent consultant, you have a contract with Athabasca University to develop an application to support +course administration. Read the following detailed description of this application, and complete the tasks listed below. + +* A course has a unique course number and title, and is assigned to one or more areas of the Computer +Science program. Each course has an instructor, at least one teaching or research assistant, an online +conference whiteboard, a time (interval), and a capacity (maximum number of participants). A course +need not be offered each term, but the challenge option is open throughout the entire year. A course +may have some prerequisite courses, and a student is not allowed to register for a course if s/he has not +fulfilled the prerequisites or their equivalent. In addition, a student who would like to challenge a course +should have the course prerequisites or authorization from the course instructor. +* An instructor has a first name, last name, email address, phone number, fax number, and is assigned to +several areas of expertise. Note that two different instructors might have the same (first and last) +names, and they might share the same fax number. +* A student is given a unique student number. For each student, we want to record first name and last +name, email address, phone number, postal address, and GPA. Again, the same names may belong to +two different individuals. A student may be enrolled in several courses each term. For each enrolment, +we want to record the grade. There are two types of student: graduate and undergraduate. +Undergraduate students have a study major, while graduate students have both a specialization and a +thesis topic. +* A teaching assistant is a graduate student, and we need to record his/her first name, last name, year, +and GPA, as well as how many courses (including the current one) the teaching assistant has already +taught. +* A research assistant is a student for whom we want to record the usual basic information, plus the +number of years of work experience. The research assistant provides laboratory support for students, +manages the conference whiteboard, and does some programming tasks. +* An area of study is described by its name. An area may be divided into several sub-areas. These areas are +used to categorize instructors, as well as courses. For example, the area of game development consists +of sub-areas such as Graphics, Networking, and Human-Computer Interaction; the area of E-Services +Technology consists of Artificial Intelligence, Networking, and Distributed Systems. + +>> Hint: For each relation without a unique attribute or combination of attributes, introducing an artificial primary key might be a good idea. + +Design an ER diagram for this course administration system. Draw the complete ER diagrams, including all aspects +discussed in the course. Clearly state any further assumptions made, but note that you must not override the +specifications above. + +### Question 4 (15 marks) + +For each of the following relations, identify the Normal Form(s) each relation satisfies, and transform it into 3NF. + +* Consider the relation STUDENT, where a student can have only one major: + ```text + RELATION = STUDENT (StuID, StuName, Major), Primary Key = {StuID}. + ``` +* Consider the relation EMPLOYEE, where an employee can have more than one specialization: + ```text + RELATION = EMPLOYEE (EmpID, Name, Specialization), Primary Key = {EmpID}. + ``` +* Consider the relation LEASE, where a person can live in only one building, and a building can charge only one rental rate: + ```text + RELATION = LEASE (PersonID, BuildingID, Rent), Primary Key – {PersonID}. + ``` + +### Question 5 (20 marks) + +Consider a one-relation database with the following attributes: + +* Employee number (emp_no), +* Date hired (date), +* Job title (job), +* Phone number (phone_no), +* Office number (office_no), +* Area (area), +* Salary (sal), +* project number (proj_no), +* Project budget (p_budget), +* Department number (dep_no), +* Department budget (d_budget), +* and Department manager employee number (mgr_emp_no). + +The following business rules apply: +* No employee can manage more than one department at a time. +* No employee can work in more than one department at a time. +* No employee can work on more than one project at a time. +* No employee can have more than one office at a time. +* No employee can have more than one phone at a time. +* No employee can have more than one job at a time. +* No project can be assigned to more than one department at a time. +* No office can be assigned to more than one department at a time. +* Department numbers, employee numbers, project numbers, office numbers, and phone numbers are all "globally" unique. + +The following functional dependencies also apply: + +* emp_no g phone, emp_no g office_no, emp_no g dep_no, emp_no g proj_no +* {emp_no, date} g job, {emp_no, date} g sal +* phone_no g office_no, office_nog area, office_no g dep_no +* proj_no g dep_no, proj_no g p_budget +* dep_no g mgr_emp_no, dep_no g d_budget +* mgr_emp_no g dep_no + +Transform this relation into 3 NF. Justify any decomposition. + +### Question 6 (15 marks) + +Consider the following relations: + + ```text + Emp(E_id: integer, E_name: string, Age: integer, Salary: real) + Works(E_id: integer, Dep_id: integer, affectation: date) + Dept(Dep_id: integer, Dep_name: string, budget: real, Manager_id: integer) + ``` +* What referential integrity constraints exist between these relations? +* What are the options for enforcing these constraints when a user attempts to delete a Dept tuple? diff --git a/assignments/Assignment1.pdf b/assignments/Assignment1.pdf new file mode 100644 index 0000000..778343b Binary files /dev/null and b/assignments/Assignment1.pdf differ -- cgit v1.2.3