Lecture Notes Unit2 chapter7 RelationalDatabaseDesign

Description:
Welcome to the comprehensive guide on Relational Database Management System (RDBMS) concepts, tailored for final year B.Sc. Computer Science students affiliated with Alagappa University. This document covers fundamental principles and advanced topics in RDBMS, offering a structured approach to understanding databases in the context of modern computing. PDF content is prepared from the text book Learn Oracle 8I by JOSE A RAMALHO.

Key Topics Covered:

Main Topic : Relational Database Design
Sub-Topic :
Features of Good Relational Database Design, Design Alternative: Larger Schema, Smaller Schemas, Atomic Domains and First Normal Form, Decomposition Using Functional Dependencies, Keys and Functional Dependencies,Trivial Functional Dependency, Boyce-Codd Normal Form, BCNF and Dependency Preservation, Third Normal Form, Higher Normal Forms, Functional-Dependency Theory, Closure of a Set of Functional Dependencies, Lossless Decomposition, Decomposition using Multivalued Dependencies, Fourth Normal Form, Database-Design Process, E-R Model and Normalization, Naming of Attributes and Relationships, Denormalization for Performance, Other Design lssues, Company Database Design Example – Design 1
, Company Database Design Example – Design 2
, Company Database Design Example – Design 3
, Modeling Temporal Data

Target Audience:
Final year B.Sc. Computer Science students at Alagappa University seeking a solid foundation in RDBMS principles for academic and practical applications.

URL for previous slides
Unit I:
Chapter 6 : Database Design and ER Model https://www.slideshare.net/slideshow/lecture-notes-unit-1-chapter-6-e-r-model/271813542
Chapter 1 : Introduction to DBMS
https://www.slideshare.net/slideshow/lecture-notes-unit1-chapter1-introduction/271392881

Unit V
Chapter 20:
https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter20-transaction/270998760
Chapter 19:
https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter19-cursor-in-pl-sql/270998251
Chapter 18: https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter18-packages-pdf/270845026
Chapter 17: https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter17-stored-procedures-and-functions/270844763
Chapter 16 : https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter16-trigger-creation/270631080
Chapter 15 : https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter-15-pl-sql-programming/270383949

Unit IV

Chapter 14 Synonym : https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter14_synonyms-pdf/270327685
Chapter 13 Users, Privileges : https://www.slideshare.net/slideshow/lecture-notes-unit4-chapter13-users-roles-and-privileges/270304806
Chapter 12 View : https://www.slideshare.net/slideshow/rdbms-lecture-notes-unit4-chapter12-view/270199683
Chapter 11 Sequence: https://www.slideshare.net/slideshow/sequnces-lecture_notes_unit4_chapter11_sequence/270134792
chapter 8,9 and 10 : https://www.slideshare.net/slideshow/lecture_notes_unit

1. RDBMS -Unit II
Chapter 7
Relational Database Design
Prepared By
Dr.S.Murugan, Associate Professor
Department of Computer Science,
AlagappaGovernmentArts College, Karaikudi.
(Affiliated by AlagappaUniversity)
Mailid: [email protected]
Reference Book:
Database System Concepts by Abraham Silberschatz, Henry
F.Korth, S. Sudharshan

2. 7.1 Features of Good Relational Database Design
➢Thegoalofarelationaldatabasedesignistogenerateasetof
relationschemaswhichisusedtostoreinformationwithout
unnecessaryredundancy.

3. 7.1.1 Design Alternative: Larger Schema
➢Thedatabasedesignformaintainingaloandetailsarelooklike
asfollows:
➢GoodDatabaseDesign(LessDataRedundancy)
➢Borrower=(customer_id,loan_number)
➢loan=(loan_number,amount)
➢Theabovedatabasedesignmaybere-designed(naturaljoinof
borrowerandloan)asfollows
➢BadDatabaseDesign(HighDataRedundancy)
➢Bor_loan=(customer_id,loan_number,amount)

4. 7.1.1 Design Alternative 1: Larger Schema
➢ThreecustomersmayavailthesingleloanamountRs.10000
withtheloannumberL-100.
➢Inbor_loanrelation,thevalueofloannumberandamountis
repeated.
➢In loan and borrower relation, the amount of each loan exactly
once.

5. 7.1.1 Design Alternative 2: Larger Schema
➢Anotheralternativedesignloan_amt_branchisderivedfrom
loanandloan_branch.

6. 7.1.2 Design Alternative: Smaller Schemas
➢AnEmployeerelationmaybedecomposedintotworelation.
➢Iftheresultantofnaturaljoinofthedecomposedrelationis
similartotheoriginalrelationthenthedecomposedrelational
databasedesignisgooddecomposition.OtherwisebadDatabase
decomposition.
➢Inthefig.7.4,theoriginalrelationandtheresultofnaturaljoin
isnotsimilar.i.e.,Lossofinformation.(Thestartdateofthe
naturaljoinisrepeatedanditismeaningless)
➢InFig,7.2and7.3thereisnolossofinformation.

7. 7.1.2 Design Alternative: Smaller Schemas

8. 7.2 Atomic Domains and First Normal Form
➢ArelationschemaRisinfirstnormalform(1NF)if
thedomainsofallattributesofRareatomic.
➢Adomainisatomicifelementsofthedomainare
consideredtobeindivisibleunits.
➢Adomainisnon-atomicifelementsofthedomainare
consideredtobedivisibleunits.
➢Exampleforatomicdomain:Age,Rollnumber
➢Examplefornon-atomicdomain:Address(doorno,
street,city,etc),Name(Firstname,Lastname,middle
name)

9. 7.2 Atomic Domains and First Normal Form
Example:depositortable.

10. 7.3 Decomposition Using Functional Dependencies
➢Afunctionaldependency(FD)isarelationshipbetweentwo
attributes,typicallybetweenthePKandothernon-keyattributes
withinatable.
For Ex: Registernumber→Name
Name is functionally dependent on Register number.
Name: Dependent attribute, Register number : Independent
➢Decomposition is the process of decomposing a larger table in to
more than one table without unnecessary redundancy.
For Ex:
Original Table: Bor_loan= (customer_id, loan_number, amount)
Decomposition Table:
➢Borrower=(customer_id,loan_number)
➢loan=(loan_number,amount)

11. 7.3.1 Keys and Functional Dependencies
➢Akeywhichisuniquelyidentifiedtherecord.Ifmore
thanonekeyisuniquelyidentifiedthenitiscalledas
superkey.Minimumnumberofsuperkeyiscalled
candidatekey.
➢Keysandfunctionaldependencies,areconstraintson
thedatabasethatrequirerelationstosatisfycertain
properties.
➢Thefunctionaldependenciescanbeusedintwoways:
1.IfarelationrislegalunderasetFof
functionaldependencies,wesaythatr
satisfiesF.
2.IfarelationrislegalunderasetFof
functionaldependencies,wesaythatF
holdsonr.

12. 7.3.1 Keys and Functional Dependencies
For example, the functional dependency
customer_street→city, holds on relation R; but not
satisfied. Because two different cities may have the
same street name.
Sowe cannot uniquely determined.

13. 7.3.1 Keys and Functional Dependencies
➢TrivialFunctionalDependency:Afunctional
dependencyoftheform
➢{Student_Id,Student_Name}->Student_Idisa
trivialfunctionaldependencyasStudent_Idisa
subsetof{Student_Id,Student_Name}.
➢Thatmakessensebecauseifweknowthevaluesof
Student_IdandStudent_Namethenthevalueof
Student_Idcanbeuniquelydetermined.

14. 7.3.2 Boyce-CoddNormal Form

15. 7.3.2 Boyce-CoddNormal Form
Bor_loan= (customer_id, loan_number, amount)
In the above relation, the functional dependency loanno→amount
holds, but loan_numberis not a super key. Because many customers
may have same loan number.
To avoid the above problem, the Bor_loantable divided into two
table;
➢Borrower=(customer_id,loan_number)
➢loan=(loan_number,amount)
Intheaboverelation,thefunctionaldependencyloanno→
amountholds,andloan_numberisasuperkey(Actually,in
thiscaseprimarykey)

16. 7.3.3 BCNF and Dependency Preservation
➢DecompositionintoBCNFcanpreventefficienttesting
ofcertainfunctionaldependencies.
➢Acustomermayhaveonlyoneemployeeas"personal
banker."Thisfollowsfromtherelationshipsetcust-
bankerbeingmany-to-onefromcustomertoemployee
asshowninFig:1.

17. 7.3.3 BCNF and Dependency Preservation
Fig 1: Customer –Bank Relation ship (many to one)

18. 7.3.3 BCNF and Dependency Preservation
➢Suppose,acustomermayhavemorethanonepersonal
banker,butatmostoneatagivenbranch.(i.e.,A
customermayhavedifferentaccountindifferent
branch,theyoperatetheiraccountviadifferentmanager
manytomanyrelationship)
➢TheE-Rdesignbymakingthecust-bankerrelationship
setmany-to-many(sinceacustomermaynowhave
morethanonepersonalbanker),andbyaddinganew
relationshipset,works-in,betweenemployeeand
branchindicatingemployee-branchpairswherethe
employeeworksinthebranchasshowninFig:7.6.

19. 7.3.3 BCNF and Dependency Preservation

20. 7.3.4 Third Normal Form
consider cust-banker-branch relationship and the functional dependency
cust_banker_branch=(customer_id, employee_id, branch_name, type)
Employeejd→branch-name. (Refer Fig 7.7)

21. 7.3.4 Third Normal Form

22. 7.3.5 Higher Normal Forms
➢Multi-valued functional dependencies are treated as
higher normal Form.
➢For ex, the employee entity set, the employee may
have several phone numbers, some of which may be
shared by multiple employees.

23. 7.4 Functional-Dependency Theory
7.4.1 Closure of a Set of Functional Dependencies
Let F be a set of functional dependencies. The closure of
F, denoted by -F
+
, is the set of all functional dependencies
logically implied by F.

24. 7.4 Functional-Dependency Theory
7.4.1 Closure of a Set of Functional Dependencies
The following three rules to find logically implied
functional dependencies. By applying these rules
repeatedly, we can find all of F+, given F. This collection
of rules is called Armstrong's axioms.

25. 7.4 Functional-Dependency Theory
7.4.1 Closure of a Set of Functional Dependencies
To simplify the functional dependency, the additional list
of rules are given below:

26. 7.4 Functional-Dependency Theory
7.4.1 Closure of a Set of Functional Dependencies
Figure 7.8 shows a procedure that demonstrates formally
how to use Armstrong's axioms to compute f+.

27. 7.4 Functional-Dependency Theory
7.4.1 Closure of a Set of Functional Dependencies
➢Theleft-handandright-handsidesofafunctional
dependencyarebothsubsetsofR.
➢Sinceasetofsizenhas2
n
subsets,thereareatotalof
2
n
x2
n
:2
2n
possiblefunctionaldependencies,wheren
isthenumberofattributesinR.
➢Ifn=3,thenpossiblefunctionaldependenciesare
2*(2
2*3
)=128

28. 7.4 Functional-Dependency Theory
7.4.4 Lossless Decomposition

29. 7.6 Decomposition using Multivalued Dependencies
7.6.1 Multivalued Dependencies
➢FunctionalDependenciesdoesnotallowthesameA
valuebutdifferentBvalue(ifA→B).Forthisreason,
functionaldependenciessometimesarereferredtoas
equalitygeneratingdependencies.
➢MultivaluedDependenciesallowsthesameAvalue
withdifferentBvalue.Forthisreason,multivalued
dependenciesarereferredtoastuplegenerating
Dependencies.

30. 7.6 Decomposition using Multivalued Dependencies
7.6.1 Multivalued Dependencies

31. 7.6.2 Fourth Normal Form

32. 7.6.2 Fourth Normal Form
Considerthebankingexample.Assumethat,inan
alternativedesignforthebankdatabaseschema,we
havetheschema
cust-loan:(loan-number,customer-id,customer-name,
customer-street,customer_city)
customer-id→customer_name,customer_street,
customer_city
customer-idisnotakeyforcust-Ioan.However,
assumethatourbankisattractingwealthycustomers
whohaveseveraladdresses(say,awinterhomeand
asummerhome).

33. 7.6.2 Fourth Normal Form
➢Theaboverelationcontainsdataredundancy.The
functionaldependencydoesnotallowsthedata
redundancy.
➢Themultivalueddependencyallowsthedata
redundancy.Theaddressofeachresidenceofa
customeronceforeachloanthatcustomerhas.
➢TosolvethisproblembydecomposingRinto:
loancust-id=(loan-numebr,customer-id)
cust_residence=(customer-id,customer-street,
customer-city)

34. 7.7 More Normal Forms
➢Thefourthnormalformisbynomeansthe
"ultimate"normalform.
➢Therearetwotypesofnormalformnamelyproject
joinnormalformanddomainkeynormalform
usinggeneralizedmultivalueddependency.

35. 7.8 Database-Design Process
➢Thedatabasedesignmaybeoneofthefollowing
➢RcouldhavebeengeneratedinconvertinganE-R
diagramtoasetofrelationschemas.
➢Rcouldhavebeenasinglerelationcontainingall
attributes.Thenormalizationprocessthenbreaks
upRintosmallerrelations.
➢Thedesignercantestwhetherthedatabasedesign
followsthenormalformrules.

36. 7.8.1 E-R Model and Normalization
➢IfthedatabasedesignerperfectlydesigntheER
modelthennoneedtogofornormalizationprocess.
➢Itisdifficulttoidentifythefunctionaldependency
fromtheERmodel.
➢Forinstance,supposeanemployeerelationhadan
attributesdepartment-numberanddepartment-
addressandthereisafunctionaldependency
department-number→department-address.
➢Inthissituation,Theemployeerelationshouldbe
normalized.

37. 7.8.1 E-R Model and Normalization
➢Intheaboveexample,ifwehaddesignedtheE-R
diagramcorrectly,wewouldhavecreatedadepartment
relationwithattributedepartment-addressanda
relationshipbetweenemployeeanddepartment.
Before Normalization
Employee : Department-number, Department Address
After Normalization
Department : Department-number, Department Address
Employee: Department-number, Employee-no, Employee-
name

38. 7.8.2 Naming of Attributes and Relationships
➢Eachattributenamehasauniquemeaninginthedatabase.
➢Theorderofattributenamesinaschemadoesnotmatter,
itisconventiontolistprimary-keyattributesfirst.
➢Inlargedatabaseschemas,relationshipsets(andschemas
derivedtherefrom)areoftennamedviaaconcatenationof
thenamesofrelatedentitysetswithhyphenorunderscore.
ForExample:loan_branch.
➢Differentorganizationshavedifferentconventionsfor
namingentities.Forexample,Theentitysetofcustomers
maycalleithercustomerorcustomers.Usingeither
singularorpluralisacceptable.

39. 7.8.3 Denormalizationfor Performance
➢Theprocessoftakinganormalizedschemaandmaking
itnon-normalizediscalleddenormalization.
➢Thedenormalizationusesthedataredundancyto
improveperformanceforbetterapplication.Butit
needsadditionalstoragespaceandtimeoverheads.

40. 7.8.4 Other Design lssues
➢Datapertainingtotimeortorangesoftimehave
severalissues.
➢Consideracompanydatabasewanttostoreearningsof
companiesindifferentyears.

41. Company Database Design Example –Design 1
EARNINGS
COMPANY_IDYEAR AMOUNT
C1001 20001780000
C1002 20001540000
C1001 20011380000
C1002 20011040000
➢Theonlyfunctionaldependencyonthisrelationis
company-id,year→amountandtherelationisin
BCNF.
➢Thisisabestdatabasedesign,becausenoneedto
createnewrelationforeveryyearandnoneedto
writequeriesforeveryyear.

42. Company Database Design Example –Design 2
EARNINGS 2000
COMPANY_IDAMOUNT
C1001 1780000
C1002 1540000
EARNINGS 2001
COMPANY_IDAMOUNT
C1001 1380000
C1002 1040000
➢Analternativedesignistousemultiplerelations,each
storingtheearningsforadifferentyear.
➢Theonlyfunctionaldependencyhereoneachrelation
wouldbecompany-id→earnings,sotheserelations
arealsoinBCNF.
➢Thisalternativedesignisclearlyabadidea-wewould
havetocreateanewrelationforeveryyear.

43. Company Database Design Example –Design 3
COMPANY_YEAR
COMPANY_ID
EARNINGS
2000
EARNINGS
2001
C1001 17800001380000
C1002 15400001040000
➢Representingthesamedataistohaveasingle
relation.
➢Thisalternativedesignisalsoclearlyabadidea-we
wouldhavetomodifythetableandwritenewqueries
foreveryyear.
➢Representationsofrelationwithonecolumnforeach
valueofanattribute,arecalledcrosstabs

44. 7.9 Modeling Temporal Data
➢Ingeneral,temporaldataaredatathathavean
associatedtimeintervalduringwhichtheyarevalid.
➢Weusethetermsnapshotofdatatomeanthevalueof
thedataataparticularpointintime.
➢Forexample,FindallcustomerswholivedinPrinceton
in1981.

45. 7.9 Modeling Temporal Data
➢Acustomer-idhasonlyonecustomerstreetand
customer-cityvalueforanygiventimet.
➢Atemporalfunctionaldependencycanberepresented
asfollows:

46. 7.9 Modeling Temporal Data
➢Theoriginalprimarykeyforatemporalrelation
wouldnolongeruniquelyidentifyatuple.
➢Toresolvethisproblem,wecouldaddthestartand
endtimeattributestotheprimarykey.
➢Thecustomerhavedifferentaddresswithdifferent
time.
Customer
CustomerNameAddressCustomer_IdStart_timeEnd_time

47. 7.9 Modeling Temporal Data
➢Insteadofstoringstartandendtimeattribute,createa
correspondinghistoryrelationthathastemporal
information,forpastvalues.
➢Forexample,inourbankdatabase,Allhistorical
informationismovedtohistoricalrelations.
➢Thecustomerrelationmaystoreonlythecurrent
address,whilearelationcustomerhistorymay
containalltheattributesofcustomer,withadditional
start-timeandend-timeattributes.

48. Company Database Design Example –Design 1
History
Customer_IdStart_timeEnd_time
Customer
CustomerNameAddressCustomer_Id
➢Thecustomer
haveonlyone
addressatatime.
Theperiodof
livingduration
canbemoved
intoHistory
relation.