er/eer to relational data model 1 database design
TRANSCRIPT
![Page 1: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/1.jpg)
ER/EER to Relational Data Model
1
Database Design
![Page 2: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/2.jpg)
Database DesignDatabase Design
2
Steps in building a database for an application:
Real-world domain
Conceptualmodel
DBMS data model
Create Schema
(DDL)
Load data(DML)
![Page 3: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/3.jpg)
Relational ModelRelational Model
3
It uses the concept of mathematical relation
In the relational model, all data is logically structured within relations (tables).
![Page 4: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/4.jpg)
Relational Data StructureRelational Data Structure
4
4170010
4182000
4182034
4188134
4189860
4192134
StudentNo
Al-Saleh
Al-Ghanem
Al-Fahad
Saod
Rashed
Al-Fahad
LName
Amal
Nora
Laila
Amal
Rana
Rania
FName Initial
M.
A.
A.
F.
I.
M.
DOB
04-06-78
02-12-79
01-11-74
22-04-73
30-01-78
19-03-79
GPA
3.91
4.20
4.01
3.01
2.31
3.50
STUDENT
Attributes
Tuples
Cad
inal
ity
Rel
atio
n
Degree
Relation’s name
![Page 5: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/5.jpg)
Relational Data StructureRelational Data Structure
5
Relation is a table with columns & rows. Holds information about entities.
Attribute is a named column of a relation.
Domain is the set of allowable values for one or more attributes. Every attribute in a relation is defined on a domain.
Tuple is a row of a relation.
![Page 6: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/6.jpg)
Relational Data StructureRelational Data Structure
6
Degree of a relation is the number of attributes it contains.
Cardinality of a relation is the number of tuples it contains.
Relational database is a collection of normalized relations with distinct relation names.
![Page 7: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/7.jpg)
Tuples
7
A tuple is a set of (<attribute,value>) pairs.These tuples are identical:
T1 = <(name, ahmad), (age, 16), ( address, Riyadh)>
T2= <(age, 16), ( address, Riyadh),(name, ahmad)>
The ordering of the tuples is not part of the relation.
![Page 8: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/8.jpg)
Domain
8
Domain : set of atomic valuesDomain Constraints : values of each
attributes must be an atomic (not divisable into components ) value from the domain.
Associated to a data type.Dom(colors): {black,red, white},
![Page 9: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/9.jpg)
DomainsDomains
9
4170010
4182000
4182034
StudentNo
Al-Saleh
Al-Ghanem
Al-Fahad
LName
Amal
Nora
Laila
FName Initial
M.
A.
A.
DOB
04-06-78
02-12-79
01-11-74
GPA
3.91
4.20
4.01
STUDENT
StudentNo
LName
FName
Initial
Student Name
Last Name
First Name
Initial
DefinitionAttribute
DOB
GPA
Domain Name
Date of Birth
Great Point Average
Digits: size 7
Character: size 15
Character: size 15
Character: size 3
Date: range 01-01-20, format dd-mm-yy
Real: size 3, decimal 2, range 0-5
![Page 10: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/10.jpg)
Relational KeysRelational Keys
10
Candidate key (CK) It’s a minimal set of attributes necessary to identify a row. Its also called minimal super key.A relation schema may have more than one key, each of the keys is called a candidate key.
Primary Key (PK) is the CK that is selected to identify tuples uniquely within the relation
Foreign Key (FK) is an attribute, or set of attributes, within one relation that matches the CK of some relation. Used to represent relationship between tuples of two relations.
![Page 11: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/11.jpg)
Relational KeysRelational Keys
11
4170010
4182000
4182034
4188134
4189860
StudentNo
Al-Saleh
Al-Ghanem
Al-Fahad
Saod
Rashed
LName
Amal
Nora
Laila
Amal
Rana
FName Initial
M.
A.
A.
F.
I.
DOB
04-06-78
02-12-79
01-11-74
22-04-73
30-01-78
GPA
3.91
4.20
4.01
3.01
2.31
STUDENT
D001
D002
D003
DeptNo
Computer Science
Business Administration
Science
Department Name
Build # 20
Dept
D001
D001
D002
D003
D001
Location
Build # 45
Build # 6
DEPARTMENTPrimary Key
Foreign Key
![Page 12: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/12.jpg)
Basic Definitions
12
Relational Keys:PK : underlined with a straight line Composite keysFK: underlined with a dashed line
Properties of tables:Each table has a unique nameEach entry stores a single valueEach row is uniqueEach column has a unique nameThere is no sequence of rows.
![Page 13: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/13.jpg)
DB RelationsDB Relations
13
Relation schema is a named relation defined by a set of attributes
If A1, A2, .., An are a set of attributes, then relation schema R is:
R = (A1, A2, .., An)Relational database schema is a set of
relation schemas, each with a distinct name If R1, R2, .., Rn are a set of relation schemas,
then relational schema R is:R= {R1, R2, .., Rn}
![Page 14: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/14.jpg)
Relation SchemaRelation Schema
14
STUDENT (StudentNo, Lname, Fname, Initial, DOB, GPA, Dept)
4170010
4182000
4182034
4188134
4189860
StudentNo
Al-Saleh
Al-Ghanem
Al-Fahad
Saod
Rashed
LName
Amal
Nora
Laila
Amal
Rana
FName Initial
M.
A.
A.
F.
I.
DOB
04-06-78
02-12-79
01-11-74
22-04-73
30-01-78
GPA
3.91
4.20
4.01
3.01
2.31
STUDENTDept
D001
D001
D002
D003
D001
![Page 15: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/15.jpg)
Relation SchemaRelation Schema
15
DEPARTMENT (DeptNo, Department Name, Location)
D001
D002
D003
DeptNo
Computer Science
Business Administration
Science
Department Name
Build # 20
Location
Build # 45
Build # 6
DEPARTMENT
![Page 16: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/16.jpg)
Relational IntegrityRelational Integrity
16
Data integrity refers to the validity, consistency, and accuracy of the data in the database.
Integrity rules are constraints that apply to all instances of the DB.
Two integrity rules for the relational model:- Entity integrity- Referential integrity
![Page 17: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/17.jpg)
Entity Integrity
17
Entity Integrity: (in a single relation)Ensures that there are no duplicate records within the table. In a base relation, no attribute of a PK can be null.T[PK] ≠ Null for any tuple in R.
![Page 18: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/18.jpg)
Referential Integrity
18
Referential Integrity: ( two relations) If a FK exists in a relation, either the FK value
must match a CK value of some tuple in its home relation or the FK value must be wholly null.
The referencing relation R1 has the FK that reference a PK in the referenced relation R2.
=> t1[FK] =t2[PK], t1 in R1 and t2 in R2
![Page 19: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/19.jpg)
Referential Integrity Constraints
19
SSN | Fname | LName | Bdate | DNO
Dnumber | Dname | MGRSSN | MgrStartDate
EMPLOYEE
DEPARTMENT
![Page 20: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/20.jpg)
Properties of Relations
20
1. The relation has a distinct name from all other relation names in the relational DB (oracle enforces this rule)
2. Each cell of the relation (attribute) contains exactly single value
3. Each attribute has a distinct name (oracle enforces this rule)
4. The values of an attribute are all of the same domain5. Each tuple is distinct. There are no duplicate tuples6. The order of attributes has no significance7. the order of tuples has no significance; theoretically.
![Page 21: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/21.jpg)
Integrity constraints
21
Domain constraintsEntity integrity constraintsReferential integrity constraintsKey ConstraintOperational constraints
![Page 22: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/22.jpg)
Operations of the Relational Model
22
Updates: changes the database’s state.InsertDeleteUpdate/modify
Retrievals: does not change the current state of the database.
![Page 23: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/23.jpg)
The Insert Operation
23
This operation can violate all constraints of the relation. How?
Domain constraint: attribute value does not apear in the corresponding domain.
Key constraint: if the key value already exist in another tuple.
Entity integrity: if the PK of the new tuple is NULLReferential integrity: if any value of the FK refers to
a tuple that does not exist in the other relation.
![Page 24: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/24.jpg)
The Delete Operation
24
This operation can violate only the referential integrity constraint. How?
E.g: you can not delete a course that many students study in already.
![Page 25: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/25.jpg)
The Update Operation
25
The new value must be of the correct data type and domain.
![Page 26: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/26.jpg)
26
![Page 27: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/27.jpg)
Step -1- Strong Entity Type
27
Represent each entity type with a relationEntity type attributes become the relation
attributese.g.
STUDENT (StudentNo, Lname, Fname, Initial, DOB, GPA, Dept)DEPARTMENT (DeptNo, Department Name, Location)
![Page 28: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/28.jpg)
Step -2- Weak Entity Type
28
A weak entity type relation must include its key and its strong entity type PK as a FK. The combination of the two keys form the PK of the weak entity.
EMPLOYEE
EmpNo
LName FName
DOBDepNo FName
DEPENDENThas
EMPLOYEE (EmpNo, Lname, Fname, DOB)
DEPENDENT (DepNo, EmpNo, FName)FK
![Page 29: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/29.jpg)
Step -3- Binary 1:1 Relationship Type
29
Identify an entity type (S) (preferably total participator) Include the PK of the other entity (T) as a FK in S Add attributes that describes the relationship
BRANCHmanageEMPLOYEE
EMPLOYEE(EmpNo, Lname, Fname, DOB)
BRANCH(BrnNo, Name, EmpNo, StartDate, EndDate)
1 1
(0,1) (1,1)
StartD EndDEmpNo
LName FName
DOBName
BrnNo
FK
………..
![Page 30: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/30.jpg)
Binary 1:1 Important Notes
30
For a 1:1 relationship, the PK of the partial is a FK in the total.
What happens if have an equal multiplicity?Then it depends on the path analysis.
![Page 31: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/31.jpg)
Step -4- Binary 1:N Relationship Type
31
Identify a participating entity type (S) on the m-side Include the PK of the other entity type (T) as a FK in S Add attributes that describes the relationship
BRANCHallocateEMPLOYEE
EMPLOYEE(EmpNo, Lname, Fname, DOB, BrnNo)
BRANCH(BrnNo, Name)
M 1
EmpNo
LName FName
DOBName
BrnNo
FK………..
![Page 32: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/32.jpg)
Binary 1:N Important Notes
32
For a 1:N relationship, the schema of the N side will have the PK of the 1 side as a foreign key.
What if the 1 side has a composite key?All keys will be copied to the many side as
foreign keys.
![Page 33: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/33.jpg)
Step -5- Binary M:N Relationship Type
33
Create a relation R to represent the relationship Include the PK of participating entity types (T & S) as FK in R. The combination of the two FK will form the PK of R Add attributes that describes the relationship
EMPLOYEE(EmpNo, Lname, Fname, DOB)
PROJECT(ProjNo, Name)
Work-on(EmpNo,ProjNo, hours)
M N
hoursEmpNo
LName FName
DOBName
ProjNo
FKFK
EMPLOYEE work-on PROJECT
![Page 34: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/34.jpg)
Binary M:N Important Notes
34
For a M:N relationship , a new schema is created that contains the PK of N and PK of M plus any other attributes.
Some times the situation of insufficient PK occurs … !! How?
E.g. a patient gets a treatment several times a resulting table will contain the PK of patient and the PK of the treatment.. But the patient may have the same treatment for several times ….. Therefore a date field must be included as a PK of the relation.
![Page 35: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/35.jpg)
Step -6- n-ary Relationship Type
35
Create a relation R to represent the relationship Include the PK of the participating entities as FK in R. The combination of all FK form the PK of R. Add attributes that describes the relationship
SUPPLIERcontractBUSINESS
BUSINESS(BizNo) LAWYER(LawNo) SUPPLIER(SupNo)
contract(BizNo, SupNo, LawNo, StartDate, EndDate)
StartD EndDBizNo
LawNo
SupNo
FK
Lawyer
FK FK
![Page 36: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/36.jpg)
Ternary Relationship Important Notes
36
If the Ternary relationship is:N:N:N then a new intersection table is
created that includes the 3 entities keys as a PK of that table.
1:N:N then the PK of the 1 is a FK in the intersection table.
![Page 37: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/37.jpg)
Recursive Relationship 1:1
37
The schema for this relationship is going to have a new field called MarriedToPersonID, which shares the same domain of the PersonID.
PERSON Married to
PersonID1
1
PERSON(PersonID, PersonName,MarriedToPersonID)
FK
![Page 38: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/38.jpg)
Recursive Relationship 1:N
38
The schema for this relationship is going to have a new field, Its exactly the same is a 1:1 uniary relationship
EMPLOYEE supervises
EmpID1
N
EMPLOYEE(EmpD, EmpName,DOB, ManagerEmpID)
FK
![Page 39: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/39.jpg)
Step-7- Multi-valued Attribute
39
Suppose A is a relation that contains the multi-valued attribute Create a relation R to represent the attribute Include the PK of A as FK in R The PK of R is the combination of the PK of A (FK) & the multi-valued attribute
EMPLOYEE
EmpNo
DOB
Tel_no
EMPLOYEE(EmpNo, DOB)
TELEPHONE(EmpNo, tel_no)FK
![Page 40: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/40.jpg)
Composite Attribute
40
Include its simple components in the relation
EMPLOYEE
emp_no
name DOB
LNameinitial FName
EMPLOYEE(EmpNo, Fname, initial, Lname, DOB)
![Page 41: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/41.jpg)
41
EER To Relational Data Model
![Page 42: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/42.jpg)
EER Relational Model
42
In EER Model, there are sub-classes and super-classes and to convert them into relational schema we have to use one of the four options which will discuss in next slides:
We have to remember these concepts which related to EER.
o Total : all entities in super class must be a member of subclass.
o Partial :an entity in super class may not belong to any subclass.
o Disjoint : an entity be a member only in one subclass.
o Over-lapping: an entity may be a member of more than one subclass
![Page 43: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/43.jpg)
EER Relational ModelMandatory(Total)/NonDisjoint(Overlapping)
43
Suppose specialization with subclasses (S1, S2, .., Sm} & a
superclass C Create a relation L to represent C with PK
& attributes Include the unshared attributes for each
subclass Si, 1<= i <= m Add discriminator to distinguish the type of
each tuple
![Page 44: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/44.jpg)
EER Relational ModelTotal/Non-Disjoint (Overlapping)
Single Relation
R
PK(K)
Boolean type attribute
o
![Page 45: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/45.jpg)
EER Relational ModelTotal/Non-Disjoint (Overlapping)
45
EMPLOYEE( EmpNo, Fname, Lname, DOB, Salary,TypingSpeed,TGrade,EngType, Secretary Flag, Technician Flag, Engineer Flag)
![Page 46: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/46.jpg)
EER Relational ModelTotal/Disjoint
46
Suppose specialization with subclasses (S1, S2, .., Sm} & a
superclass C Create a relation Li, 1≤ i ≤m, to represent
each combination of super/subclass
![Page 47: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/47.jpg)
EER Relational ModelTotal/Disjoint
Relation R2 for subclass
TRUCK
Relation R1 for subclass
CAR
PK(K)
Tonnage
d
![Page 48: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/48.jpg)
EER Relational ModelTotal/Disjoint
d
Car( VehicleId ,LicensePlateNo, Price, MaxSpeed ,NoOfPassengers)
Truck ( VehicleId ,LicensePlateNo, Price,NoOfAxles,Tonnago)
![Page 49: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/49.jpg)
EEROptional/NonDisjoint(Overlapping)
49
Suppose specialization with subclasses (S1, S2, .., Sm} & a
superclass C Create a relation L to represent C with PK
& attributes Create a relation Li to represent each
subclass Si, 1≤ i ≤m
![Page 50: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/50.jpg)
Relation R1
EEROptional/NonDisjoint(Overlapping)
Relation R
Relation R2
Relation R3
PK(K)O
![Page 51: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/51.jpg)
EEROptional/NonDisjoint(Overlapping)
O
Employee( SSN ,Fname,Minit,Lname,BirthDate,Address,JobType)Secretary( SSN ,TypingSpeed)Technician( SSN , Tgrade)Engineer( SSN ,EngType)
![Page 52: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/52.jpg)
EEROptional/Disjoint
52
Suppose specialization with subclasses (S1, S2, .., Sm} & a
superclass C Create a relation L1 to represent C with PK
& attributes Create a relation L2 to represent all
subclasses Si, 1≤ i ≤ m Add discriminator to distinguish the type of
each tuple
![Page 53: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/53.jpg)
Relation R1
EEROptional/Disjoint
Relation R
Relation R2
Relation R3
PK(K)d
![Page 54: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/54.jpg)
EEROptional/Disjoint
54
EMPLOYEE ( EmpNo, Fname, Lname, DOB, Salary)SECRETARY( EmpNo, TypingSpeed)TECHNICIAN( EmpNo, Tgrade)ENGINEER( EmoNo, EngType)
![Page 55: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/55.jpg)
Views
55
Base relation is a named relation corresponding to an
entity in the conceptual schema, whose tuples are physically stored in the DB
View is a derived relation. Virtual, may not exist, but dynamically derived from one or more base relations.
The only information about a view that is stored in the database is its structure.
The external model can consist of both conceptual level relations (base relations) and derived views.
![Page 56: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/56.jpg)
View
56
![Page 57: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/57.jpg)
Purpose of Views
57
Provides security mechanism by hiding parts of the DB from certain users
Customize data to user’s needs, so that the same data can be seen by different users in different ways
Simplify complex operations. It allow you to work with data from different tables simultaneously.
Supports logical data independence
![Page 58: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/58.jpg)
Updating Views
58
All updates to a base relation should be immediately reflected in all views that reference that base relation.
All updates to a view should be reflected in the underlying base relation, under the following rules:- Updates are allowed through a view defined using a
simple query involving a single base relation & containing either the PK or a CK of the base relation
- Update are NOT allowed through views involving multiple base relations
- Updates are not allowed through views involving aggregation or grouping operations
![Page 59: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/59.jpg)
Relational Integrity
59
Data integrity refers to the validity, consistency, and accuracy of the data in the database.
Integrity rules are constraints that apply to all instances of the DB.
Two integrity rules for the relational model:- Entity integrity- Referential integrity
![Page 60: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/60.jpg)
Relational Integrity
60
Entity Integrity: Ensures that there are no duplicate records within the table. In a base relation, no attribute of a PK can be null
Referential Integrity: If a FK exists in a relation, either the FK value must match a CK value of some tuple in its home relation or the FK value must be wholly null.
Enterprise constraints: rules specified by the users or DBA of the DB based on the ways an organization perceives and uses its data
(e.g.number of staff working in a branch is at most 20)
![Page 61: ER/EER to Relational Data Model 1 Database Design](https://reader038.vdocuments.us/reader038/viewer/2022102719/5697bf8d1a28abf838c8c47d/html5/thumbnails/61.jpg)
References
61
Elmasri,Ramez &Navathe,Shamkant B., Fundamentals Of Database System, fifth edition, Pearson ,2007
Fatimah Alakeel’s Lectures http://webdocs.cs.ualberta.ca/~drafiei/291/notes/4.
ER2Rel.pdfhttp://www.cs.sfu.ca/CC/354/zaiane/material/notes/
Chapter2/node10.html#SECTION00190000000000000000
http://web.uettaxila.edu.pk/CMS/seADMSbsSp09/notes%5CADBMS-Lecture-6%20ER%20to%20Relational%20Mapping.pdf
www.cs.gsu.edu/~cscbecx/.../Chapter%207.ppthttp://www.mscs.mu.edu/~praveen/Teaching/
Fa06/Db/Lectures/ch7.pdf
www.cs.uwm.edu/classes/cs557/powerpoint/chapter9.ppt