Database Fundamentals - ESP

3y ago
66 Views
3 Downloads
214.38 KB
31 Pages
Last View : 14d ago
Last Download : 6m ago
Upload by : Kian Swinton
Transcription

DatabaseFundamentalsRobert J. RobbinsJohns Hopkins Universityrrobbins@gdb.orgFile: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 1

What is a Database?General: A database is any collection of related data.Restrictive: A database is a persistent, logically coherentcollection of inherently meaningful data, relevantto some aspects of the real world.The 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 database.File: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 2

What 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 DBMSshould be envisioned as a multi-layered system:External Level(individual user views)Conceptual Level(Enterprise-wide view\)Internal Level(storage view)Storage Level(physical storage)File: N drive:\jhu\class\1995\db-fund.pptExternalView 1 ExternalView nConceptualSchemaInternalSchemaPhysicalDatabase 1994, 1995 Robert RobbinsDatabase Fundamentals: 3

What Does a DBMS Do?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 optimizationFile: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 4

Who Interacts with a DBMS?Many different individuals are involved with adatabase management system over its life: systems analysts database designers database administrators application developers usersFile: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 5

Components of a Database SystemDirect ablesFileManagerSystemCatalogFile: N abase 1994, 1995 Robert RobbinsMetadataDatabaseDatabase Fundamentals: 6

Relational Database ModelWhat is a relational database? 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 tuplesFile: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 7

Basic Set ATIONexampleNote:any collection of distinct entities of anysort.A { 1,2,3,4,5,6 }B { H,T }C { R,B }D { Grant, Sherman, Lee }a set of ordered pairs, produced bycombining each element of one set witheach element of another set.B x C { H,R , H,B , T,R , T,B }Cartesian products may be generated bymultiplying any number of sets together.The actual number of sets involved in aparticular case is said to be the “degree”or “arity” of that Cartesian product.a subset of a Cartesian productQ { H,R , H,B }Relations may be of any degree (arity).File: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 8

Basic Set ConceptsA set is usually indicated by including a commadelimited 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. Two setsare considered equivalent if and only if theycontain exactly the same members, without regardfor the order in which the members are listed.R { 1,2,3,4,5,6 } { 3,2,1,6,4,5 }G { Marshall, Eisenhower, Bradley } { Bradley, Marshall, Eisenhower }File: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 9

Basic Set ConceptsAn ordered double (or triple or quadruple or ntuple) is usually indicated by including a commadelimited list of the names its members within apair of pointed brackets:S 2,4 C Marshall, Eisenhower, Bradley Order must be maintained in ordered n-tuples.Two tuples are considered different if they containthe same members in a different order.S 2,4 4,2 C Marshall, Eisenhower, Bradley Bradley, Eisenhower, Marshall A set may consist of an unordered collection ofordered tuples. For example, we could imaginethe set of all ordered pairs of integers, such thatthe first element is the square root of the secondelement.R { 1,1 , 2,4 , 3,9 . }As this ellipsis indicates, sets can beinfinite in size. However, sets thatare actually represented in a databasemust be finite.File: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 10

Basic Set ConceptsLETR be the set of possible outcomes when rollinga single red die.R { 1,2,3,4,5,6 }LETB be the set of possible outcomes when rollinga single blue die.B { 1,2,3,4,5,6 }THENThe Cartesian product R x B gives the set ofoutcomes when the two dice are rolledtogether:R x B: {File: N drive:\jhu\class\1995\db-fund.ppt 1,1 1,2 1,3 1,4 1,5 1,6 3,1 3,2 3,3 3,4 3,5 3,6 5,1 5,2 5,3 5,4 5,5 5,6 2,1 2,2 2,3 2,4 2,5 2,6 4,1 4,2 4,3 4,4 4,5 4,6 6,1 6,2 6,3 6,4 6,5 6,6 } 1994, 1995 Robert RobbinsDatabase Fundamentals: 11

Relation: Subset of a Cartesian ProductSet RSet B112233445566A Cartesian product of two setscan be generated by combiningevery member of one set withevery member of the other set.This 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 set.Starting two sets. 1,1 1,2 1,3 1,4 1,5 1,6 3,1 3,2 3,3 3,4 3,5 3,6 5,1 5,2 5,3 5,4 5,5 5,6 2,1 2,2 2,3 2,4 2,5 2,6 4,1 4,2 4,3 4,4 4,5 4,6 6,1 6,2 6,3 6,4 6,5 6,6 A Cartesian product of two sets,shown as a list of ordered pairs.File: N drive:\jhu\class\1995\db-fund.ppt112233445566A Cartesian product of two sets,shown as a connection diagram,with each member of the first setconnected to each member of theother set. 1994, 1995 Robert RobbinsDatabase Fundamentals: 12

Relation: Subset of a Cartesian Product 1,1 1,2 1,3 1,4 1,5 1,6 A Cartesian product pairs everymember of the first set with everymember of the second set. 2,1 2,2 2,3 2,4 2,5 2,6 A relation pairs somemembers of the first setwith some members ofthe second set. 3,1 3,2 3,3 3,4 3,5 3,6 1,1 2,2 3,3 4,4 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 File: N drive:\jhu\class\1995\db-fund.ppt 5,5 6,6 A relation, therefore, must alwaysbe representable as a subset of someCartesian product. 1994, 1995 Robert RobbinsDatabase Fundamentals: 13

Relation: Set of Ordered TuplesA 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. Generally, we couldrepresent a set of ordered doubles as below. S1 is the first set and S2 the second.S1xS2 By 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 n.S1 xS2xS3xx Sn File: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 14

Relations as a DatabaseAn n-ary relation (i.e., 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, etc.S1 xS2xS3xxSn Codd recognized that many of the files used in computerized informationsystems were very similar in structured to tabularized relations.SmithRobertL.1154 Elm StreetGlendaleMD21200SmithJudyF.1154 Elm StreetGlendaleMD21200JonesGregG.765 Cedar LaneTowsonMD21232HarrisLloydK.2323 Maple DrTowsonMD21232 Ziegler FredFile: N drive:\jhu\class\1995\db-fund.ppt K. 7272 Cherry Ln. 1994, 1995 Robert Robbins Baltimore MD 21208Database Fundamentals: 15

Relations as a DatabaseThe business data file resembles a relation in a number of ways. 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 pSmithRobertL.1154 Elm StreetGlendaleMD21200SmithJudyF.1154 Elm StreetGlendaleMD21200JonesGregG.765 Cedar LaneTowsonMD21232HarrisLloydK.2323 Maple DrTowsonMD21232 Ziegler Fred K. 7272 Cherry Ln. Baltimore MD 21208If 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 nullFile: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 16

Relations as a DatabaseAn 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 key.Sometimes 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. Either patient number (assigned bythe hospital) or social security number (brought with the patient) arepossibilities. In addition, one might argue that the combination of last name,address, and birth date could collectively serve as a key.Any attribute or set of attributes that might possibly serve as a key is knownas a candidate key. Keys that involve only one attribute are known assimple keys. Keys that involve more than one attribute are composite keys.patient #SS #Last Nameaddressbirth dateP-64122123-45-6789Smith123 Main Street10 MAY 44P-75642001-32-6873Pedersen1700 Cedar Barn Way31 MAR 59P-70875444-44-5555Wilson1321 North South St7 AUG 90P-79543555-12-1212Grant808 Farragut Avenue1 DEC 66 P-71536 888-88-8888 MacPherson 1617 Pennsylvania Ave11 APR 60In designing a database, one of the candidate keys for each relation must bechosen to be the primary key for that table. Choosing primary keys is acrucial task in database design. If keys need to be redesignated, the entiresystem may have to be redone. Primary keys can never be null and shouldnever be changed. Sometimes none of the candidate keys for a relation arelikely to remain stable over time. Then, an arbitrary identifier might be createdto serve as a primary key. Such arbitrary keys are also known as surrogatekeys.File: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 17

Relations as a DatabaseA binary relation (i.e., a subset of a Cartesian product of two sets) could be berepresented in a computer system as two-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. For example, a binary relation could beused to provide unique three-letter identifiers for academic departments.Additional relations could be used to give more information about individualdepartments or individual faculty members.ZOLZoologyPSDPolitical ScienceCPSComputer ScienceHISHistory ACC AccountingZOLZoologyRoom 203Natural Science Bldg355 4640CPSComputer ScienceRoom 714AWells Hall355 5210BSPBiological ScienceRoom 141Natural Science Bldg353 4610CEMChemistryRoom 320Chemistry Bldg355 9175 PSDRoom 303Political Science 355 6590South Kedzie Hall999-99-9999JohnsonWilliamF.1533 Raleigh Dr.BaltimoreMD21211888-88-8888JohnsonWilliamF.2842 Colony Ave.BaltimoreMD21201777-77-7777BrownJamesG.99 W. East St.TowsonMD21232666-66-6666BrownGwenK.99 W. East St.TowsonMD21232 111-11-1111 ZieglerFile: N drive:\jhu\class\1995\db-fund.ppt Samual L. 7272 Cherry Ln. 1994, 1995 Robert Robbins Baltimore MD 21208Database Fundamentals: 18

Relations as a DatabaseYet another relation could be used to show what faculty were members of whatdepartments. Notice that faculty member 999-99-9999 is a member of morethan one department and that, even on this short list, the department of zoologyhas two members 66-66-6666ZOL 999-99-9999BSPRelations of this sort, that combine identifiers from two other relations, providethe “glue” that holds a relational database together. other fieldsMember-of RelationèSS NumberSS NumberDepartments RelationèçFaculty RelationDept CodeDept Codeother fields Whenever the values in an attribute column in one table “point to” primary keysin another (or the same) table, the attribute column is said to be a foreign key.Columns containing foreign keys are subject to an integrity constraint: anyvalue present as a foreign key must also be present as a primary key.File: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 19

Relational Database OperatorsData models consist of data structures andpermitted operations on those data structures.Part of Codd’s genius was to recognize thatmany of the standard set operators that can takerelations as operands map nicely to real datamanipulation problems: Cartesian product union intersection differenceCodd devised some additional operators toprovide extra manipulatory power: select project join divideThe operators have now been extended toinclude more useful manipulations: outer join outer unionFile: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 20

Relational Database Normal FormsConsiderable study has been made of the properties of relations as they affectthe behavior of relational databases. The results of these studies are captured inthe definition of normal forms.First Normal Form: A relation is in first normal form (1NF)if and only if all underlying domainscontain atomic values only.Second Normal Form: A relation is in second normal form(2NF) if and only if it is in 1NF andevery non-key attribute is fullydependent on the primary key.Third Normal Form: A relation is in third normal form (3NF)if and only if it is in 2 NF and the nonkey attributes are mutuallyindependent.File: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 21

What is the E-R Data Model?The Entity-Relationship (E-R) data model is asemantically rich model that can be mapped to arelational system. other fieldsMember-of RelationèSS NumberSS NumberDepartments RelationèçFaculty RelationDept CodeDept Codeother fields The three files represented above are all relations in the formal sense. Chen(1976) noted that different relations may play different roles in a database andthat being able to recognize and document those roles is a key part of databasedesign. The “faculty” and the “department” relations above both storeinformation about particular real-world entities. The “member-of” relation, onthe other hand, stores information about specific relationships involvingindividual pairs of real-world entities.File: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 22

The E-R Data ModelDifferent needs for access and use of thedatabase can be supported through differentuser viewsExternalView 1ExternalView n Logical data independenceoccurs here.ConceptualDatabasePhysical data independenceoccurs here.PhysicalDatabaseDefinition and mappingwritten in data definitionlanguageImplemented on physicaldevices, using a commercialdatabase productFile: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 23

The E-R Data ModelLayers may be added to a conceptual design inorder to increase the semantic richness availableat the top design level.ExternalView 1 ConceptualDatabase(E-R)Although the E-R approachdoes not require an underlying relational model, mostE-R models can be convertedto relational models lDatabaseFile: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsExternalView nTheentity-relationshipapproach (Chen, 1976)improved the mappingbetween the semantics of adatabase design and thatportion of the real worldbeing modeled with thedata.Codd’s relational model(1970) provided the firstformal basis for databasedesign.Database Fundamentals: 24

The E-R Data ModelIf layered conceptual models are used, the layering may be perceived differentlyby the system’s users and developers. Users often see the database only in termsof the views that they employ. System analysts and designers may thinkprimarily about the E-R schema, whereas the database administrator is likely todeal primarily with the relational schema and the physical system.ExternalView 1Moving between conceptualmodels can be difficult,especially if automatedtools to facilitate the moveare not available. al)If a commercial RDBMS isused, a relational conceptualmodel provides a basis fordesigning and implementingan underlying physicaldatabase.File: N drive:\jhu\class\1995\db-fund.pptExternalView nA different conceptual modelmay be necessary to capturethe semantics of the databasedomain.If a commercial relationaldatabase system is used,mapping from a relationalconceptual model to thephysical database should berelatively straightforward.PhysicalDatabase 1994, 1995 Robert RobbinsDatabase Fundamentals: 25

E-R Data Model: Graphical ConventionsSets of real-world entities are represented withnamed rsesRelationships between members of entity setsare represented with named diamonds that areconnected to the rectangles of the participatingentity sets with directed arcs:Departments4,nmajorsin1,2StudentsArcs are drawn with an orientation that “points” from foreign keys to primarykeys. The min:max participation cardinality can be indicated by placingpairs of numbers on each arc. Here, “4,n” means that every department isrequired to have at least four student majors, but can have many more; “1,2”means that each student is required to have at least one major and is permittedto have no more than two majors. Sometimes only the maximum participationcardinalities are shown.File: N drive:\jhu\class\1995\db-fund.ppt 1994, 1995 Robert RobbinsDatabase Fundamentals: 26

E-R Data Model: Graphical ConventionsMany different cardinalities are possible.Documenting the cardinalities is an essentialpart of database analysis and design.One-to-one:Entity Set AMany-to-one:Entity Set A1nRelatesRelates11Entity Set BEntity Set BOne-to-many:Entity Set A1nEntity Set BRelatesmEntity Set BRelates1:nEntity Set BRelates0:nEntity Set BRelatesMany-to-many:Entity Set AOne-to-many:(mandatory)Entity Set AOne-to-many:(optional)Entity Set AFile: N drive:\jhu\class\1995\db-fund.pptn1:11:1 1994, 1995 Robert RobbinsDatabase Fundamentals: 27

E-R Data Model: ExamplesFaculty and departments entities could berelated by a many-to-many acultyThey could also be related by a one-to-one“chai

A database management system (DBMS) is a collection of programs that enables users to create and maintain a database. According to the ANSI/SPARC DBMS Report (1977), a DBMS should be envisioned as a multi-layered system: Conceptual Schema Physical Database Internal Schema External View 1

Related Documents:

Kodak AiO Ink Technology Deep Dive ESP 1.2 / 3.2ESP 1.2 / 3.2 Hero 9.1Hero 9.1 Hero 7.1Hero 7.1 Office Hero 6.1Office Hero 6.1 Hero 5.1Hero 5.1 Hero 3.1Hero 3.1 2011‐ 2013 ESP C110, C310, C315 ESP Office 2150/2170 ESP Office 6150 ESP 7250 ESP 9250 2010‐ ESP 3250 ESP 5250 ESP3 ESP5 ESP7 ESP9 2011 2008‐ 2009 Easyshare 5100 Easyshare 5300 .

ESP 88 and ESP 00 can support a total of 4 cards per ESP for a total of 16 mic/ line inputs and 16 line outputs. The ESP-88 comes pre-loaded with two 4x4 mic/line cards, allowing two additional 4x4 cards to be added. The ESP-00 does not include these cards, so 8 audio input or output cards can be added. PC 0

1 subl 12 , %esp 1 function : 2 movl 3 , 8(%esp ) 2 pushl %ebp 3 movl 2 , 4(%esp ) 3 movl %esp , %ebp 4 movl 1 , (%esp ) 4 subl 16 , %esp 5 c a l l function. Pushes the base pointer (EBP) in the stack, now it’s a saved frame pointer (SFP). Moves the stack pointer (ESP) in EBP, subst

(Postgres) OSISoft Historian A-MQ. ESP Replacement Business Optimization - BPMN. ESP Demo Walkthrough ESP Vibration Simulation ESP Remote Metrics ESP Remote Control. Global visibility to Production KPIs Demo ESP Machine Health Scenario . SSL/TLS – ensure the wire is encrypted

All ESP course designers should know that the job of an effective ESP course design includes: (a) planning and designing an appropriate course suitable for the target ESP groups, (b) deciding on the practical type of syllabus that well meets the basic requirements of the ESP course participants. 1. Theoretical Background to ESP Course Design

The ESP-MC, ESP-SAT and ESP-SITE-Satellite controllers are available as metal wall-mount (WM) or stainless steel pedestal-mount (SS) models, with 8-, 12-, 16-, 24-, 28, 32, 36- or 40-station capability. All configurations have the following special features: Anti-rus

ESP-PSRAM64 and ESP-PSRAM64H are 64 Mbit serial pseudo SRAM devices that are organized in 8Mx8 bits. They are fabricated using the high-performance and high-reliability CMOS technology. ESP-PSRAM64 operates at 1.8V and can offer high data bandwidth at 144 MHz clock rate, while ESP-PSRAM64H o

Jul 17, 2020 · rwc-44lpm-esp * rwc-44lpm-rtf * rwc-56lpm-esp * rwc-56lpm-rtf * rwc-68lpm-esp * rwc-68lpm-rtf * rwc-80lpm-esp * rwc-80lpm-rtf revised 07/17/2020. what’s in the ox? (reessed suite) . figure 10 figure 11. step 5 continued example of installation best to leave hardware loose until bo