Add assignment 1

2 files changed, 129 insertions, 0 deletions

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
--- /dev/null
+++ b/assignments/Assignment1.pdf