1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
|
## Question 1
* **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." - Modern Database Management: 10th edition by Hoffer, Ramesh, Topi
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.
* **Briefly contrast and compare the following development approaches: the systems development life cycle and the prototyping methodology.**
The `system development life cycle` is a set of steps that include the following phases:
* 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 `prototyping methodology` is an iterative process that allows realizing value sooner.
1. identify problem
1. design prototype
1. implement prototype (iterate on prototype)
1. push to production and release to users.
* **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
**Give a simple example of an E-R diagram for each of the following concepts:**
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.
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.
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 can be a subgroup of group.
## Question 3
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.
**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.**
Assumptions:
* An instructor can also be a student.
* Different instructors can teach a course but not for the same term.
Rationale:
* A discipline represents an area of interest.
* A discipline can be a child of another discipline. (Represented as a unary relationship)
* Each course can have 0 or more disciplines through the `course_disciplines` relation. (Represented as an Associative entity.)
* A term represents a duration of time where courses can be offered.
* A course may or may not be offered during a term.
* Each student and instructor has a profile record. (Represented as a weak entity)
* An instructor can also be a student. This would yield a instructor record, student record and a single shared profile record.
* A scheduled course is a Associative entity that represents the offering of a course during a specific term.
## Question 4
**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}
```
* Passes: 1st normal form (0 multi-valued attributes)
* Passes: 2nd normal form (0 functional dependencies)
* Passes: 3rd normal form (0 transitive dependencies)
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}
```
* **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)
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:**
```text
RELATION = LEASE (PersonID, BuildingID, Rent), Primary Key – {PersonID}.
```
* Passes: 1st normal form (0 multi-valued attributes)
* Fails: 2nd normal form (1 functional dependency. Rent is determined by BuildingID)
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
Consider a one-relation database with the following attributes:
**Transform this relation into 3NF. Justify any decomposition.**
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.
* A phone and office number can be own by a single employee so both relations have a foreign key to the employees table with a unique constraint.
* 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 with a unique constraint.
* 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.
* A unique constraint on `departments(manager_id);` to ensure an employee cannot manage more than one department.
Example postgresql SQL:
```sql
CREATE TABLE employees (
id bigint primary key,
department_id bigint,
project_id bigint,
hired_at date,
title varchar(255),
area varchar(255),
salary decimal
);
CREATE TABLE departments (
id bigint primary key,
manager_id bigint references employees(id),
budget decimal
);
CREATE TABLE projects (
id bigint primary key,
department_id bigint references departments(id),
budget decimal
);
CREATE TABLE phones (number varchar(255) primary key, employee_id bigint references employees(id));
CREATE TABLE offices (number varchar(255) primary key, employee_id bigint references employees(id));
ALTER TABLE employees ADD CONSTRAINT departments_fk FOREIGN KEY (department_id) REFERENCES departments(id);
ALTER TABLE employees ADD CONSTRAINT projects_fk FOREIGN KEY (project_id) REFERENCES projects(id);
CREATE UNIQUE INDEX department_manager_idx on departments(manager_id);
CREATE UNIQUE INDEX employee_phones_idx on phones(employee_id);
CREATE UNIQUE INDEX employee_offices_idx on offices(employee_id);
```
## Question 6
Consider the following relations:
```
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?**
* `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?**
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.
|
