Database Fundamentals - ESP

1 Database Fundamentals : 1 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsDatabaseFundamentalsRobert J. RobbinsJohns Hopkins Fundamentals : 2 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsWhat is a Database ?General: A Database is any collection of related : A Database is a persistent, logically coherentcollection of inherently meaningful data, relevantto some aspects of the real portion of the real world relevant to the Database is sometimes referredto as the universe of discourse or as the Database miniworld. Whatever itis called, it must be well understood by the designers of the Fundamentals : 3 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsWhat is a Database Management System?A Database management system (DBMS) is acollection of programs that enables users to createand maintain a Database . According to theANSI/SPARC DBMS Report (1977), a DBMS should be envisioned as a multi-layered system:ConceptualSchemaPhysicalDatabaseI nternalSchemaExternalView 1 ExternalView nExternal Level(individual user views)Conceptual Level(Enterprise-wide view\)Internal Level(storage view)Storage Level(physical storage) Database Fundamentals : 4 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsWhat Does a DBMS Do?

2 Database management systems provide severalfunctions in addition to simple file management: allow concurrency control security maintain data integrity provide for backup and recovery control redundancy allow data independence provide non-procedural query language perform automatic query optimizationDatabase Fundamentals : 5 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsWho Interacts with a DBMS?Many different individuals are involved with adatabase management system over its life: systems analysts Database designers Database administrators application developers usersDatabase Fundamentals : 6 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsComponents of a Database SystemDMLP rocessorApplicationProgramsDirect UserQueriesDatabaseDescriptionTablesDDLC ompilerDatabaseAdministratorConcurrentAc cessTablesAuthorizationTablesFileManager DatabaseManagerPhysicalSystemDatabaseMet adataDatabaseSystemCatalogDatabase Fundamentals : 7 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsRelational Database ModelWhat is a relational Database ?

3 A Database that treats all of its data as acollection of relationsWhat is a relation? a kind of set a subset of a Cartesian product an unordered set of ordered tuplesDatabase Fundamentals : 8 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsBasic Set Conceptsany collection of distinct entities of set of ordered pairs, produced bycombining each element of one set witheach element of another subset of a Cartesian productRELATION examplesA = { 1,2,3,4,5,6 }B = { H,T }C = { R,B }D = { Grant, Sherman, Lee }exampleB x C = { <H,R>,<H,B>,<T,R>,<T,B> }exampleQ = { <H,R>,<H,B> }Note:Cartesian products may be generated bymultiplying any number of sets actual number of sets involved in aparticular case is said to be the degree or arity of that Cartesian :Relations may be of any degree (arity). Database Fundamentals : 9 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsBasic Set ConceptsA set is usually indicated by including a comma -delimited list of the names its members within apair of wavy brackets:R = { 1,2,3,4,5,6 }G = { Marshall, Eisenhower, Bradley }The members of a set are unordered.

4 Two setsare considered equivalent if and only if theycontain exactly the same members, without regardfor the order in which the members are = { 1,2,3,4,5,6 } = { 3,2,1,6,4,5 }G = { Marshall, Eisenhower, Bradley } = { Bradley, Marshall, Eisenhower } Database Fundamentals : 10 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsBasic Set ConceptsOrder must be maintained in ordered tuples are considered different if they containthe same members in a different = < 2,4 > < 4,2 >C = < Marshall, Eisenhower, Bradley > < Bradley, Eisenhower, Marshall >An ordered double (or triple or quadruple or n-tuple) is usually indicated by including a comma -delimited list of the names its members within apair of pointed brackets:S = < 2,4 >C = < Marshall, Eisenhower, Bradley >A set may consist of an unordered collection ofordered tuples.

5 For example, we could imaginethe set of all ordered pairs of integers, such thatthe first element is the square root of the = { <1,1>,< 2,4 >,<3,9> .. }As this ellipsis indicates, sets can beinfinite in size. However, sets thatare actually represented in a databasemust be Fundamentals : 11 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsBasic Set ConceptsLETB be the set of possible outcomes when rollinga single blue = { 1,2,3,4,5,6 }LETR be the set of possible outcomes when rollinga single red = { 1,2,3,4,5,6 }The Cartesian product R x B gives the set ofoutcomes when the two dice are rolledtogether:THENR x B:<1,1> <1,2> <1,3> <1,4> <1,5> <1,6> <2,1> <2,2> <2,3> <2,4> <2,5> <2,6> <3,1> <3,2> <3,3> <3,4> <3,5> <3,6> <4,1> <4,2> <4,3> <4,4> <4,5> <4,6> <5,1> <5,2> <5,3> <5,4> <5,5> <5,6> <6,1> <6,2> <6,3> <6,4> <6,5> <6,6>{} Database Fundamentals : 12 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsRelation.

6 Subset of a Cartesian Product 123456 Set R123456 Set BStarting two Cartesian product of two setscan be generated by combiningevery member of one set withevery member of the other results in a complete set ofordered pairs, consisting ofevery possible combination ofone member of the first setcombined with one member ofthe second set. The number ofelements in a Cartesian productis equal to M x N, where M andN give the number of membersin each Cartesian product of two sets,shown as a list of ordered pairs.<1,1> <1,2> <1,3> <1,4> <1,5> <1,6> <2,1> <2,2> <2,3> <2,4> <2,5> <2,6> <3,1> <3,2> <3,3> <3,4> <3,5> <3,6> <4,1> <4,2> <4,3> <4,4> <4,5> <4,6> <5,1> <5,2> <5,3> <5,4> <5,5> <5,6> <6,1> <6,2> <6,3> <6,4> <6,5> <6,6>123456123456A Cartesian product of two sets,shown as a connection diagram,with each member of the first setconnected to each member of theother Fundamentals : 13 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsRelation.

7 Subset of a Cartesian Product <1,1> <1,2> <1,3> <1,4> <1,5> <1,6> <2,1> <2,2> <2,3> <2,4> <2,5> <2,6> <3,1> <3,2> <3,3> <3,4> <3,5> <3,6> <4,1> <4,2> <4,3> <4,4> <4,5> <4,6> <5,1> <5,2> <5,3> <5,4> <5,5> <5,6> <6,1> <6,2> <6,3> <6,4> <6,5> <6,6> <1,1> <2,2> <3,3> <4,4> <5,5> <6,6>A relation, therefore, must alwaysbe representable as a subset of someCartesian Cartesian product pairs everymember of the first set with everymember of the second relation pairs somemembers of the first setwith some members ofthe second Fundamentals : 14 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsRelation: Set of Ordered TuplesBy adding sets, relations can be extended to include ordered triples, orderedquadruples or, in general, any ordered n-tuple, as below. A relation with nparticipating sets is said to be of degree n or to possess arity binary relation is a set of ordered doubles, with one element a member of thefirst set and one element a member of the second set.

8 Generally, we couldrepresent a set of ordered doubles as below. S1 is the first set and S2 the x S2 S1 x S2 x S3 x x Sn Database Fundamentals : 15 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsRelations as a DatabaseCodd recognized that many of the files used in computerized informationsystems were very similar in structured to tabularized relations. S1 x S2 x S3 x x Sn An n-ary relation ( , a subset of a Cartesian product of n sets) could be berepresented in a computer system as an n-column tabular file, with one memberfrom the first set named in the first column of each record and one member ofthe second set in the second column, Elm Street1154 Elm Street765 Cedar Lane2323 Maple Cherry Database Fundamentals : 16 File: N_drive:\jhu\class\1995\ 1994, 1995 Robert RobbinsRelations as a DatabaseThe business data file resembles a relation in a number of ways.

9 The tabularfile itself corresponds to a relation. Each column, or attribute, in the filecorresponds to a particular set and all of the values from a particular columncome from the same domain, or set. Each row, or record, in the filecorresponds to a tupleSmithSmithJonesHarrisRobertJudyGreg Lloyd1154 Elm Street1154 Elm Street765 Cedar Lane2323 Maple Cherry Name-L Name-F MI address city state zipDomains(sets)If such a file is to be genuinely interchangeable with a relation, certaincontraints must be met: every tuple must be unique every attribute within a tuple must be single-valued in in all tuples, the values for the same attribute must come from thesame domain or set no attributes should be nullDatabase Fundamentals : 17 File: N_drive.

10 \jhu\class\1995\ 1994, 1995 Robert RobbinsRelations as a Database P-64122P-75642P-70875P-79543P-71536123-4 5-6789001-32-6873444-44-5555555-12-12128 88-88-8888 SmithPedersenWilsonGrantMacPherson123 Main Street1700 Cedar Barn Way1321 North South St808 Farragut Avenue1617 Pennsylvania Ave10 MAY 4431 MAR 597 AUG 901 DEC 6611 APR 60 patient # SS # Last Name address birth dateAn essential attribute of a relation is that every tuple must be unique. Thismeans that the values present in some individual attribute (or set of attributes)must always provide enough information to allow a unique identification ofevery tuple in the relation. In a relational Database , these identifying valuesare known as key values or just as the more than one key could be defined for given table. Forexample, in the table below (which represents, perhaps, a patient record file),several columns might serve as a key.