From b879d419650922117f2da0aefae19bf11b2016e0 Mon Sep 17 00:00:00 2001 From: mo khan Date: Mon, 30 Dec 2019 15:33:02 -0700 Subject: rename to README --- assignments/01.md | 129 ------------------------------------------------ assignments/1/README.md | 129 ++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 129 insertions(+), 129 deletions(-) delete mode 100644 assignments/01.md create mode 100644 assignments/1/README.md diff --git a/assignments/01.md b/assignments/01.md deleted file mode 100644 index aedd06c..0000000 --- a/assignments/01.md +++ /dev/null @@ -1,129 +0,0 @@ -# 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/1/README.md b/assignments/1/README.md new file mode 100644 index 0000000..aedd06c --- /dev/null +++ b/assignments/1/README.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? -- cgit v1.2.3