This is the topics of Database management System and write on the basis of Mid-term examination. Hope this few problems are more helpful for students and if they solve, it enhance their conceptual ...
Analisis Metodeologi Pengembangan Sistem
Here, solve few exercise problem about Scenario to ER Diagram, Relational Model to ER Diagram and ER Diagram to Relational Model.
Questions and Answers from Chapter 2 of Database Concepts by Kroenke and Auer. To be used as a supplement for studying MIS concepts.
Here, solve few exercise problem about Scenario to ER Diagram, Relational Model to ER Diagram and ER Diagram to Relational Model.Full description
Here, solve few exercise problem about Scenario to ER Diagram, Relational Model to ER Diagram and ER Diagram to Relational Model.Full description
Here, solve few exercise problem about Scenario to ER Diagram, Relational Model to ER Diagram and ER Diagram to Relational Model.Full description
dbmsFull description
Marketingul Relational - referat
Descripción: SmartPlant 3D Data Model
Full description
Relational Data Model At the end of this this unit, you should be able to :
Understand the terminology used in Relational Model Defne the rules which preserve the integrity o a relational database Describe the operations in relational languages that are used in updating or retrieving data
Relational Model Terminology
Relational Data Model Based on the mathematical concept o a relation Organizes and represents data in a orm o a table Relat elatio ion n Attri ttribu bute te !uple !uple Domain
A tab table le with with rows ows and and colu column mns s ame a med d colu column mn o o a rel relat atio ion n Row o a relation relation "et o allowable#valid$ values or each attribute% #e&g& integers'strings'($ Degr Degree ee o&& o att o attri ribu bute tes# s#co colu lumn mns$ s$ in in each each rel relat atio ion n )ardinali o& o tuples#rows$ in the relation ty *+ample o a relation "tudent
Degree is 7, Cardinality is 5 More on Domain..(data type?) Eah tuple assigns a !alue to eah attribute from its domain "or e#ample, the domain of $ust%name is of character strings strings of of ma# length &'(!arhar&'), an age from a domain of integers bet*een + and
'+, birthDate from a domain of date,, a gender might ome from domain -male, female, unno*n/
,iew vs Base Relation
0ase Relation A named relation corresponding corresponding to an entity in conceptual schema' whose tuples are physically stored in the database ot a derived relation -as physical e+istence .hile it can manipulate the conceptual or physical relations stored in the data' it does not provide security
1ie* !he dynamic result o one one or more relational operations operating on base relations to produce another relation A virtual relation that does not necessarily actally e+ist in the database but is produced upon re/uest' at time o re/uest #virtual e+istence$ As views are dynamic' changes made to base relations that a0ect view attributes are immediately re1ected in the view #i base relations update values o attributes%attributes in view will also be a0ected$ )ontents o a view are defned as a /uery on one or more base relations Modifcation through a view#e&g& insert'update'delete operations not permitted2$
Derived
Relation !able !able 4ile Alternate Terminology
!uple Row Record
Attribute )olumn 4ield
2roperties of Relations Relation name is D3"!3)! rom all other relation names in relational schemas e+actly O* atomic value *ach cell o relation contains e+actly *ach attribute as a D3"!3)! name
,alues o an attribute are A55 rom the same domain *ach tuple is distinct' there are no DU653)A!* tuples#record$ Order o attributes 7 tuples are O! important
Description Minimal set o attributes that can uni/uely identiy each tuple in a relation 3s a subset o the super key A relation may have one or more candidate 8eys )andidate 8eys can
)onditions%)riteria ;& Must Must con conta tain in uni/ue values <& Must Must not not contai contain n null values = since a null value is not guaranteed to be uni/ue 7 we would not be able to identiy each > uni/uely
consist o a single attribute or multiple attributes#together$ Multiple attribute 8eys are 8nown as composite key
@& Must Must con conta tain in minimum no& o felds#attributes$ to ensure uni/ueness#as above$
One candidate 8ey is ;& Mini Minima mall set set o attributes designated as primary <& 5ess 5ess li8 li8ely to have have 8ey whose values are its value changed used to identiy tuples @& 5ess 5ess li8 li8ely to lose lose uni/uely in a relation uni/ueness in the ote 9 .hen choosing uture a primary 8ey rom a & .ith .ith ewe ewest st pool o candidate characters 8eys' always choose a & *asier *asier to use use ro rom m single simple 8ey over the userCs point o a composite view 8ey#student3d $ )andidate 8eys that "ame as above are not selected to be primary 8eys An attribute or a set o More this later( 5in8ed to Reerential 3ntegrity attributes within one relation that matches the primary 8ey o some#possibly the same relation$ A oreign 8ey is an attribute which provides a logical lin8 between tables
5ulls incomplete%e+ceptional data& data& 6urpose 9 a way to deal with incomplete%e+ceptional Represents a value or an attribute that is currently un8nown or is Represents not applicable or this tuple OR represent an un8nown piece o data
Relational 6ntegrity
3ntegrity Rules )an be specifed on a relational database schema *nsure accuracy 7 integrity o the data in the database #Applied to data items to ma8e sure that the data is valid 7 consistent$ < 6rincipal Rules ;& *ntity 3ntegrity 6rimary 8ey in a base relation must not contain any nulls .hy A primary 8ey is a minimal identifer that is used to identiy tuples uni/uely& uni/uely& -ence' no subset o the primary 8ey is suEcient to provide uni/ue identifcation o tuples& *+ample 9
Description F !he image cncptG&gi shows !able !able D*6!' which has three columns9 D*6!O' DAM*' and 5O)&!he D*6!O column has a 6rimary :eyFFno :eyFFno row may duplicate a value in the 8ey and no null values are allowed&!his illustration shows that two rows cannot be inserted into !able D*6! because one duplicates an e+isting value in the primary 8ey' while the other contains a null value or the primary 8ey http9%%docs&oracle&com%cd%B;H@GI>G;%server http9%%docs&oracle&com%cd %B;H@GI>G;%server&;G<%b;<int&htmJi@KK; &;G<%b;<int&htmJi@KK;
<& Reerential 3ntegrity 3 oreign 8ey e+ists in a relation' either oreign 8ey value must match a candidate 8ey value o some tuple in its home relation or oreign 8ey must be wholly null 6urpose *nsures that relationships between tuples in related relations are consistent and valid AD ensures that related tuples are 3 AL*!>)OD* not accidentally deleted% in )ustomer changed !able !able is not *+ample J; ot violated 9
ound in Agent !able' !able' we would have a problem in identiying the agent o this customer& !here is a data integrity
Example #2 Violated : In the example scenario shown in Figure4.2, the record for customer O!rain "pin shaded$ in the %&'(O)E* ta+le "dependant ta+le$ is distur+ing the referential integrit of the data+ase +ecause there exist no reference for the records -E/(0%O1E -E/(0%O1E "34$ in the related ta+le, -E/( -E/( "primar ta+le$
Example # Violated :
3n 4igure <;FK' <;FK' the reerential integrity constraint ensures that every value in the mgr column o the emp table corresponds to a value that currently e+ists e+ists in theempno column o the same table' but not necessarily in the same row' because every manager must also be an employee& !his integrity constraint eliminates the possibility o erroneous employee numbers in the mgr column&
*+ample @ J ,iolated 7 ot ,iolated 9
'ome points to remem+er for referential integrit •
ou ou cannot delete delete a parent parent record record i any e+isting e+isting child child record record is there& there& 3 you have to frst delete the child record beore deleting the parent record& 3n the above e+ample you cannot delete row o employee no& ;G; since itCs child e+ist in the A!!*DA)* table& -owever' you can delete the row o employee no& ;G@ since no child record e+ist or this employee in A!!*DA)* A!!*DA)* table& 3 you defne the 4OR*3L :* with O D*5*!* )A")AD* option then you can delete the parent record and i any child record e+ist it will be automatically deleted&
*nterprise )onstraints Additional rules that are specifed by the users or database administrator that the data must satisy *&g& business rules = no more than < students to be assigned to a mentor group
Relational Algebra Relational algebra operations wor8 on one or more relations to Relational defne another relation without changing the original relations Both operands and results are relations Uses 9 F Defne a scope o retrieval F Defne a scope or update F Defne a view F Defne access rights F Defne integrity constraints
Operations 9 ;& "*5*)! operation#horizontal2$ otation 9 sigma "elect a subset o the tuples in a relation that satisy a selection condition ote9 "*5*)! operation in relational algebra has nothing to do with "N5 8eyword select& "election in relational algebra returns those tuples in a relation that ulfll a condition2 *+amples
<& 6RO*)! operation#vertical2$ Used to select certain attributes rom the relation and discards the other attributes #Delete unwanted columns rom relation$ *+amples 9
P)ombining "*5*)!3O 7 6RO*)!3O
@& U3O operation#R "$ !he result is a relation that includes all tuples that are either in relation R or in relation " or in both& Duplicate tuples are elimintated& Relation with tuples rom R and " with duplicates removed& Relation *+ample 9 R
R
S First
First
LastLast
Age
Age
!ill
!iltlh 'm
'mith
22
22
'all
' reaelln
reen
25
25
)ar
6) eeanr
6een
2
2
(on
7o(noens
7ones
2
2
•
'how the name and ran of those Forrest Emploees who are not in the %' 1on7uan department or -d
-d
" 1ept 9 %'$
"*an 9
ump 'how the 8names of all emploees woring in the %' •
department:
1e)arco
2=
" *esults: name
1ept 9 %'
"E);$ $
"E);$ $
Name
*esult:
'mith Name
Rank
reen
-ssistant
'mith First
-ssociate
S Last
Age
Forrest
ump
8
'all
reen
25
1on7uan
1e)arco
2=
& 3!*R"*)!3O operation#R "$ !he result is a relation that includes all tuples in both relation R 7 " #Relation #Relation with tuples that appear in both R 7 "$ *+ample 9 R
S First
'all
Last
reen
Age
25
& D344*R*)* operation#R = "$ First
Last
Age
!ill
'mith
22
)ar
6een
2
(on
7ones
2
!he result is a relation that includes all tuples that are in relation R
but not in relation " *+ample 9
I& )AR!*"3A 6RODU)!#Q$ !he operation is used to combine tuples rom two relations so that related tuples can be identifed My defnition = repeat the second relation *mployees *mployee "m "mith
K& O3 operation# $ !his operation is used to combine related tuples rom two relations into a single tuple !ypes o oins9 3nner oin = )ombines tuples rom two relations using comparison operators in a condition F )ondition can be be S T' ' S ' TS ' %S F */uioin#when the oin condition is S $' atural oin#operator is P$ *&g& "elect P rom *mployee emp 3*R O3 Area area on area&*mp3d S emp&*mp3d F atural oin is same as e/uioin but the di0erence is that in natural oin' the common attribute only appear once F *&g& "elect P rom *mployee emp A!URA5 A!URA5 O3 Area area on area& *mp3d S emp&3dV *+ample at 9 http9%%avacodeimpl&blog http9%%avacodeimpl&blogspot&sg%m"y"o
W& Outer oin 8eep all the tuples in the )ombine tuples rom two relations and 8eep result even i
there is no matching tuple in the relations *+ample *M6 J ; < @
