Format assignment 1

2 files changed, 87 insertions, 152 deletions

diff --git a/assignments/1/README.md b/assignments/1/README.md
index 5d9e7ba..44c54eb 100644
--- a/assignments/1/README.md
+++ b/assignments/1/README.md
@@ -1,10 +1,9 @@
 # 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.
+* 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.
 
@@ -12,117 +11,66 @@ Answer all of the following questions, and upload your completed work here.
 
 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.
-
-> "The separation of data descriptions from the application programs that use the data."
+* **Explain the concept of program-data independence, and explain how it is achieved in the database approach.**
 
-The idea between `program-data independence` is to decouple the software program from the representation
-of the data. This allows the data and the program to evolve independent of one another.
+  > "The separation of data descriptions from the application programs that use the data." - Modern Database Management: 10th edition by Hoffer, Ramesh, Topi
 
-* Briefly contrast and compare the following development approaches: the systems development life cycle and the prototyping methodology.
+  The idea between `program-data independence` is to decouple the software program from the representation
+  of the data. This allows the data and the program to evolve independent of one another.
 
-The `system development life cycle` is a set of steps that include the following phases:
+* **Briefly contrast and compare the following development approaches: the systems development life cycle and the prototyping methodology.**
 
-* planning: gain understanding of the problem to solve and the benefit of solving the problem.
-* analysis: analyze the problem thoroughly to determine requirements.
-* design: write specs for the possible solution.
-* implementation: write software to solve problem.
-* maintenance: repair, enhance and monitor system.
+  The `system development life cycle` is a set of steps that include the following phases:
 
-The `prototyping methodology` is an iterative process that allows realizing value sooner.
+  * planning: gain understanding of the problem to solve and the benefit of solving the problem.
+  * analysis: analyze the problem thoroughly to determine requirements.
+  * design: write specs for the possible solution.
+  * implementation: write software to solve problem.
+  * maintenance: repair, enhance and monitor system.
 
-1. identify problem
-1. design prototype
-1. implement prototype (iterate on prototype)
-1. push to production and release to users.
+  The `prototyping methodology` is an iterative process that allows realizing value sooner.
 
-* Discuss the difference between entity type and entity instance.
+    1. identify problem
+    1. design prototype
+    1. implement prototype (iterate on prototype)
+    1. push to production and release to users.
 
-An `entity type` is a description of the data the describes the common attributes for data in a collection. i.e. the table definition.
-An `entity instance` is a specific occurrence of the `entity type`. .i.e. a specific row in a table.
+* **Discuss the difference between entity type and entity instance.**
+  * An `entity type` is a description of the data the describes the common attributes for data in a collection. i.e. the table definition.
+  * An `entity instance` is a specific occurrence of the `entity type`. .i.e. a specific row in a table.
 
 ### Question 2 (15 marks)
 
 Give a simple example of an E-R diagram for each of the following concepts:
 
-a. Associative entity
+a. **Associative entity**
   > An entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances.
-
-  Project ||-<- Policy ->-|| SoftwareLicense
+  `Project ||-<- Policy ->-|| SoftwareLicense`
 
   ![associative entity example](./associative-entity.png)
 
-b. Weak entity
-
-  A strong entity is a type of entity that exists independent of any other entity types.
-  A weak entity is a type of entity that exists when some other type exists so it is dependent on an identifying owner.
-
-```text
-  Customer (id, name)
-  Address(id, customer_id, street, city, country)
-```
-
-  Customer --<- Dependent
+b. **Weak entity**
+  * A strong entity is a type of entity that exists independent of any other entity types.
+  * A weak entity is a type of entity that exists when some other type exists so it is dependent on an identifying owner.
+  `Customer --<- Dependent`
 
   ![weak entity example](./weak-entity.png)
 
-c. Unary relationship
+c. **Unary relationship**
 
   A unary relationship is a relationship between the instances of a single entity type.
   This is a self referential or recursive relationship.
 
-  Group is subgroup of group.
-
   ![unary relationship example](./unary-relationship.png)
 
+  Group can be a subgroup of group.
+
 ### 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
-    * graduate students have both a specialization and a thesis topic.
-  * undergraduate
-    * Undergraduate students have a study major
-* A teaching assistant is a graduate student, and we need to record his/her 
-  * first name
-  * last name
-  * year
-  * 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.
-  * the area of game development consists of sub-areas such as 
-    * Graphics
-    * Networking
-    * Human-Computer Interaction
-  * E-Services Technology consists of 
-    * Artificial Intelligence
-    * Networking
-    * 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.
+**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.**
 
   ![course administration ERD](./course-administration.png)
 
@@ -144,39 +92,42 @@ Rationale:
 
 ### Question 4 (15 marks)
 
-For each of the following relations, identify the Normal Form(s) each relation satisfies, and transform it into 3NF.
+**For each of the following relations, identify the Normal Form(s) each relation satisfies, and transform it into 3NF.**
+
+1. **Consider the relation STUDENT, where a student can have only one major:**
+  ```
+  RELATION = STUDENT (StuID, StuName, Major), Primary Key = {StuID}
+  ```
 
-* Consider the relation STUDENT, where a student can have only one major:
-```text
-RELATION = STUDENT (StuID, StuName, Major), Primary Key = {StuID}.
-```
   * Passes: 1st normal form (0 multi-valued attributes)
   * Passes: 2nd normal form (0 functional dependencies)
   * Passes: 3rd normal form (0 transitive dependencies)
 
-Alternative 3NF:
+  Alternative 3rd Normal Form (3NF)
 
-```text
-STUDENTS(id, name, major_id), primary key = {id}, foreign key = {MAJORS(major_id)}
-MAJORS(id, name), primary key = {id}
-```
+  ```text
+  STUDENTS(id, name, major_id), primary key = {id}, foreign key = {MAJORS(major_id)}
+  MAJORS(id, name), primary key = {id}
+  ```
 
-* Consider the relation EMPLOYEE, where an employee can have more than one specialization:
-	```text
+* **Consider the relation EMPLOYEE, where an employee can have more than one specialization:**
+	```
 	RELATION = EMPLOYEE (EmpID, Name, Specialization), Primary Key = {EmpID}.
 	```
   * Passes: 1st normal form (0 multi-valued attributes)
   * Passes: 2nd normal form (0 functional dependencies)
   * Fails: 3rd normal form (Specialization is a transitive dependency)
 
-3NF:
-```text
-Employee(id, name), Primary Key = {id}
-Specialization(id, name), Primary Key {id}
-EmployeeSpecialization(employee_id, specialization_id), Primary Key {employee_id, specialization_id}
-```
+  3rd Normal Form (3NF)
+
+  ```
+  EMPLOYEE(id, name), Primary Key = {id}
+  SPECIALIZATION(id, name), Primary Key {id}
+  EMPLOYEESPECIALIZATION(employee_id, specialization_id), Primary Key {employee_id, specialization_id}
+  ```
+
+* **Consider the relation LEASE, where a person can live in only one building, and a building can charge only one rental rate:**
 
-* 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}.
 	```
@@ -184,12 +135,13 @@ EmployeeSpecialization(employee_id, specialization_id), Primary Key {employee_id
   * Passes: 1st normal form (0 multi-valued attributes)
   * Fails: 2nd normal form (1 functional dependency. Rent is determined by BuildingID)
 
-3NF
-```text
-Lease(id, person_id, building_id), Primary Key = {id}, Foreign Key = {person(person_id}, {buildings(building_id)}
-Person(id), Primary Key = {id}
-Building(id, rent), Primary Key = {id}
-```
+  3rd Normal Form (3NF)
+
+  ```
+  LEASE(id, person_id, building_id), Primary Key = {id}, Foreign Key = {PERSON(person_id}, Foreign Key = {BUILDING(building_id)}
+  PERSON(id), Primary Key = {id}
+  BUILDING(id, rent), Primary Key = {id}
+  ```
 
 ### Question 5 (20 marks)
 
@@ -197,19 +149,6 @@ Consider a one-relation database with the following attributes:
 
 ![one-relation](./question-5-before.png)
 
-* 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.
@@ -222,45 +161,41 @@ The following business rules apply:
 * Department numbers, employee numbers, project numbers, office numbers, and phone numbers are all "globally" unique.
 * No office can be assigned to more than one department at a time.
 
-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 3NF. Justify any decomposition.
+**Transform this relation into 3NF. Justify any decomposition.**
 
   ![one-relation](./question-5-after.png)
 
-* A separate projects relation has a surrogate key with a foreign key to the departments relation to ensure that a project can only be own by one department.
-* A phone and office relation can use the number as the primary key to ensure that the number is unique and not null.
-* Each employee relation has a surrogate key used to identify the employee.
-* Each employee relation has a foreign key reference to the department they work for.
-* The employee relation includes a foreign key to the project that they are currently working on.
-* Each department has a foreign key to the employees table to specify the single manager of the department.
-* Primary keys ensures global uniqueness within the relation and not null values.
-* Foreign key constraints ensure that a valid corresponding relation must exist in the relation that the foreign key references.
+  Justification
+
+  * A separate projects relation has a surrogate key with a foreign key to the departments relation to ensure that a project can only be own by one department.
+  * A phone and office relation can use the number as the primary key to ensure that the number is unique and not null.
+  * Each employee relation has a surrogate key used to identify the employee.
+  * Each employee relation has a foreign key reference to the department they work for.
+  * The employee relation includes a foreign key to the project that they are currently working on.
+  * Each department has a foreign key to the employees table to specify the single manager of the department.
+  * Primary keys ensures global uniqueness within the relation and not null values.
+  * Foreign key constraints ensure that a valid corresponding relation must exist in the relation that the foreign key references.
 
 ### 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)
-```
+  ```
+  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)
+  ```
 
-1. What referential integrity constraints exist between these relations?
+1. **What referential integrity constraints exist between these relations?**
 
-The `Works` relation has a foreign key to the `Dept` table via the `Dep_id` attribute.
-The `Dept` relation has a foreign key to the `Emp` table via the `Manager_id` attribute.
+    * `Emp.E_id` is a primary key
+    * `Works.(E_id, Dep_id)` is a composite primary key
+    * `Dept.(Dep_id)` is a primary key
+    * The `Works` relation has a foreign key to the `Dept` table via the `Dep_id` attribute.
+    * The `Dept` relation has a foreign key to the `Emp` table via the `Manager_id` attribute.
 
-2. What are the options for enforcing these constraints when a user attempts to delete a Dept tuple?
+2. **What are the options for enforcing these constraints when a user attempts to delete a Dept tuple?**
 
-If a foreign key constraint is added for each identified foreign key, then an attempted
-deletion from the `Dept` table would produce an anomaly if any rows in the `Works` relation
-has a foreign key reference to the `Dept` relation that a user was attempting to delete.
+    If a foreign key constraint is added for each identified foreign key, then an attempted
+    deletion from the `Dept` table would produce an anomaly if any rows in the `Works` relation
+    has a foreign key reference to the `Dept` relation that a user was attempting to delete.
diff --git a/assignments/1/README.pdf b/assignments/1/README.pdf
index 0e44f31..ec21537 100644
--- a/assignments/1/README.pdf
+++ b/assignments/1/README.pdf