Database Systems Development Data Modelling Using the Entity - Relationship Model |
Theoretical Knowledge |
|
|
|
|
Practical Knowledge |
|
|
|
Basic idea: There is a well-defined process by which an application is conceived, developed and implemented
1. Definition stage
2. Development stage
3. Installation & Operation
Basic idea: Easier to assess an existing system than an imaginary one
So, system is developed by rapid implementation of prototype system and this is refined until the client is satisfied.
Four step process:
Attribute Types
Entity Types - set of entities that have the same attributes
schema student (stuid, stfn, stln, major, credits )
Instances
( s1015
Jones
Mary
Math
42 )
Key Attributes of An Entity Type
Value sets ( domain ) of attributes - Specifies the set of values that may be assigned to that attribute, for each individual entity
student
stuid |
stln |
stfn |
major |
credits |
s1015 |
Jones |
Mary |
math |
42 |
s1005 |
Lee |
Perry |
history |
3 |
s1001 |
Smith |
Tom |
history |
90 |
s1010 |
Burns |
Edward |
art |
63 |
s1002 |
Chin |
Ann |
math |
36 |
s1013 |
Mccarthy |
Owen |
math |
0 |
s1020 |
Rivera |
Jane |
csc |
15 |
class
course# | facid | sched | room |
art103a | f101 | mwf9 | h221 |
hst205a | f115 | mwf11 | h221 |
csc201a | f105 | tuthf10 | m110 |
mth101b | f110 | mtuth9 | h225 |
csc203a | f105 | mthf12 | m110 |
mth103c | f110 | mwf11 | h225 |
faculty
facid | facname | dept | rank |
f101 | Adams | art | professor |
f115 | Smith | history | associate |
f105 | Tanaka | csc | instructor |
f110 | Byrne | math | assistant |
f221 | Smith | csc | professor |
enrolment
course# |
stuid |
grade |
art103a |
s1001 |
a |
csc201a |
s1020 |
b |
csc201a |
s1002 |
f |
art103a |
s1010 |
|
art103a |
s1002 |
d |
mth101b |
s1020 |
a |
hst205a |
s1001 |
c |
mth103c |
s1010 |
|
mth103c |
s1002 |
b |
department
deptname |
location |
math |
st. george |
art |
erindale |
history |
scarborough |
csc |
st. george |
Initial Designing of Student Database
1. student (stuid, stfn, stln, major, credits)
2. class (course #, facid, sched, room)
3. faculty (facid, facname, dept, rank)
4. enrolment (course#, stuid, grade)
5.department (deptname, location)
Relationship Types - Specifies the number of relationship instances that an entity can participate
Three Main Types of Relationship:
one-to-one (1:1)
one-to-many (1:n)
many-to-many (m:n)
Attributes of Relationship Types
Relations
1. dept - student 1:n
2. dept - faculty 1:n
3. enrol n:m
4. faculty - class 1:n
Practical Knowledge
a) Conceptual analysis - era
entity | attribute |
component | cname |
ccode* | |
price | |
supplier | |
supplier | sname |
saddress | |
scode* |
b) Logical design
c) Structural design
- files accessed
- fields for selection
- fields for join
- fields for retrieving values
- type of update
- files updated
- fields used
- fields changed by update
d) Physical design
relation | attribute | data type | value set |
component | cname | string(30) |
x(30) |
ccode* | string(7) |
xxx9999 |
|
price | f.p(8) |
99999.99 |
|
supplier | string(30) |
x(30) |
For the following relation that stores information about students living in dormitories at a college, draw the ER diagram
college (stuname, stuid, homeadd, homephone, dormroom, roommate_name, dormadd, status, mealplan, roomcharge, mealcharge)
1. Each student is assigned to one dormitory room and has at most one roommate. Names of students are not unique.
2. The college has several dorms. Dormroom contains a code for the dorm and the number of the particular room assigned to the student. For example, a221 means adams hall, room 211. dorm names are unique.
3. The dormadd is the address of the dorm building. Each building has its own unique address. For example, adams hall may be 123 main street, toronto, m5n 1h4.
4. Status tells the student's status: freshman, sophomore, junior, senior of graduate student.
5. Mealplan tells how many meals per week the student has chosen. Each meal plan has a single mealcharge associated with it.
6. The roomcharge is different for different dorms, but all students in the same dorm pay the same amount