13/10/20171 Lecture 2 A relation is a table with columns and rows.• Only applies to logical structure of the database, not thephysical structure. Attribute is a named column of a relation. Domain is the set of allowable values for one ormore attributes. Tuple is a row of a relation. Degree is the number of … Continue reading “logical structure of the database | My Assignment Tutor”
13/10/20171 Lecture 2 A relation is a table with columns and rows.• Only applies to logical structure of the database, not thephysical structure. Attribute is a named column of a relation. Domain is the set of allowable values for one ormore attributes. Tuple is a row of a relation. Degree is the number of attributes in a relation. Cardinality is the number of tuples in a relation. Relational Database is a collection of normalizedrelations with distinct relation names.13/10/2017213/10/20173Entity Integrity• In a base relation, no attribute of a primary keycan be null.Referential Integrity• If foreign key exists in a relation, either foreignkey value must match a candidate key value ofsome tuple in its home relation or foreign keyvalue must be wholly null.General (Business) Constraints• Additional rules specified by users or databaseadministrators that define or constrain someaspect of the enterprise.13/10/20174Primary Key• Candidate key selected to identify tuples uniquelywithin relation.Alternate Keys• Candidate keys that are not selected to be primarykey.Foreign Key• Attribute, or set of attributes, within one relation thatmatches candidate key of some (possibly same)relation.8 When modelling a database, the developer must identifythe candidate keys for each relation !! The primary key is usually underlined. If a relation has only one candidate key, then that candidatekey is also the primary key. If we have more than one candidate, we need to decide on oneto be our primary key.Student ( StudentID, Name, NIS, Age, Address, Course)Primary key13/10/20175 A foreign key is an attribute in a relation thatis not the primary key in that relation but itis the primary key in another relation. In other words: A foreign key is a field in atable that is not a primary key in that tablebut it is a primary key in another table! The reason why we create foreign keys is inorder to link tables together and it is theessence of relational modelling.10 Why do we use Foreign keys. Take a look at the following two tables oflibrary system: 1SharonBarkingHND12JohnIlfordHND13JerryRomfordBSc 1011/1/014 weeksJohnBarkingHND11/2/011022/1/011 weekJerryRomfordBSc5/1/011032/1/011 weekJohnBarkingHND13/1/01 Bookloan(LoanID, Date, Period, Name, Address, Course, return_date)Student ( StudentID, Name, Address, Course)13/10/2017611 Problems with such design:• Duplication of data.• Data redundancies.• Cause of errors. 1SharonBarkingHND12JohnIlfordHND13JerryLondonBSc 1011/1/014 weeksJohnBarkingHND11/2/011022/1/011 weekJerryRomfordBSc5/1/011032/1/011 weekJohnBarkingHND13/1/01 Bookloan(LoanID, Date, Period, Name, Address, Course, return_date)12 Solution: Create a foreign key in BookLoantable that links to the primary key in theStudent record table. 1SharonBarkingHND12JohnIlfordHND13JerryLondonBSc 1011/1/014 weeks21022/1/011 week31032/1/011 week2 Bookloan(LoanID, Date, Period,Student_ID) 13/10/20177 Try to guess some of these keys:which ones are primary key andwhich ones are foreign keys.Student ( StudentID, Name, NIS, Age, Address, Course)Book ( Title, Author, Subject, Category, ISBN)BookLoan ( LoanID, StudentID, ISBN, Date, Period, Penalty)Lecture ( LectureID, UnitID, Date, Time, Classroom ) Primary KeyForeign keys Any Questions?14 We have two terms to understand:• Entity Integrity: it is a principle thatPrimary keys cannot have repetitions orNull values.• Referential Integrity: relates to thelinking between tables defined by theprimary key and foreign key. The ideahere is that you cannot have a foreign keythat doesn’t exist in the original table.13/10/20178 1SharonBarkingHND12JohnIlfordHND13JerryLondonBSc 1011/1/014 weeks21022/1/011 week31032/1/011 week2 Now suppose, Jerry drops off the University. You need to deletehis record what would happen?16 1SharonBarkingHND12JohnIlfordHND1 1011/1/014 weeks21022/1/011 week31032/1/011 week2 DeletingJerrywouldleave loanbook tablelinking tonowhere!! ???? Referential Integrity option, ensures that if you deleteJerry, it would delete all his records (including those inother tables!!)13/10/20179 Entity Integrity• In a base relation, no attribute of a primary key can be null. Referential Integrity• If foreign key exists in a relation, either foreign key value mustmatch a candidate key value of some tuple in its home relation orforeign key value must be wholly null. General Constraints• Additional rules specified by users or database administratorsthat define or constrain some aspect of the enterprise Null• Represents value for an attribute that is currently unknown or notapplicable for tuple.• Deals with incomplete or exceptional data.• Represents the absence of a value and is not the same as zero orspaces, which are values.Superkey• An attribute, or set of attributes, that uniquelyidentifies a tuple within a relation.Candidate Key• Superkey (K) such that no proper subset is asuperkey within the relation.• In each tuple of R, values of K uniquely identifythat tuple (uniqueness).• No proper subset of K has the uniquenessproperty (irreducibility).13/10/20171019 Each relation MUST have an attribute (orcombination of attributes) that uniquely identify thatrelation. (In other words) Each table MUST have an field (orcombination of fields) that uniquely identify thattable.Student ( StudentID, Name, NIS, Age, Address, Course)Candidate KeyCan any of these two be a the PRIMARY KEY?20 Each table MUST have a field (or combination offields) that uniquely identify that table.Lecture ( WeekNumber*, UnitID, Date, Time, Classroom )Candidate KeyBecause each unit can have only one lecture a week: Thecombination of the two fields together constitute what we call aCOMPOSITE KEY.13/10/201711 1:1 (One to One)• Person has only one blood group 1:M (One to Many)• One customer may place many orders, but eachorder is placed by a single customer M:N (Many to Many)• one order may involves many items (products) andone item (product) is involved in many ordershttp://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi41.htm• Database Schema: The description of a database.Includes descriptions of the database structure andthe constraints that should hold on the database.• Schema Diagram: A diagrammatic display of(some aspects of) a database schema.• Schema Construct: A component of the schema oran object within the schema, e.g., STUDENT,COURSE.• Database Instance: The actual data stored in adatabase at a particular moment in time. Also calleddatabase state (or occurrence).13/10/201712Standard terminology and generalarchitecture for database system wasproduced in 1971 by the DBTG (DatabaseTask Group) appointed by the appointedby the Conference on Data Systems andLanguage (CODASYL, 1971).DBTG recognized two-level approachwith a system view called the schemaand user view called sub schemas.The American National StandardsInstitute (ANSI) Standards Planning andRequirements Committee (SPARC),produced a similar terminology andapproach with a system catalog.The ANSI – SPARC three levelarchitecture13/10/201713• All users should be able to access samedata but have a different customized viewof the data.• A user’s view is immune to changes madein other views.• Users should not need to know physicaldatabase storage details.DBA should be able to change databasestorage structures without affecting theusers’ views.Internal structure of database should beunaffected by changes to physicalaspects of storage.DBA should be able to changeconceptual structure of database withoutaffecting all users.13/10/201714 External Level• The users’ view of the database. Thislevel describes the part of the databasethat is relevant to each user• Users’ view of the database.• Describes that part of database that isrelevant• to a particular user.13/10/201715 Conceptual Level The community view of the database. Thislevel describes what data is stored in thedatabase and the relationship among the data• Community view of the database.• Describes what data is stored in database andrelationships among the data.• All entities, their attributes and their relationship;• Constraints on the database• Security and integrity information Internal Level• The physical representation of the database on thecomputer. This level describes how the data isstored in the database.• Physical representation of the database on thecomputer.Describes how the data is stored in thedatabase. (storage space allocation fordata and indexes; record descriptions forstorage etc.)13/10/20171613/10/201717External Level• The users’ view of the database. Alsoknown as the applications view.Conceptual Level• The overall view of the database. Alsoknown as the global view.Internal Level• The physical representation of thedatabase on the computer. Also knownas the storage view. The overall description of the database is called thedatabase schema. There are 3 different types ofschema in the database. External Schema• There are multiple external schemas (orsubschemas), each one corresponding to adifferent view of the data. Conceptual Schema• There is one conceptual schema, which describesthe data stored in the database, the relationshipsand the integrity constraints. Internal Schema• There is one internal schema, which describes howthe data are stored in the database and how theyare accessed.13/10/201718Database-Introduction-1 Supplement 01 (a)InventoryH RFinanceProductionCustomerServiceConceptualInternalExternalExternalLogical Data IndependenceRefers to immunity of external schemas tochanges in conceptual schema.Conceptual schema changes (e.g. addition/ removal of entities). Should not require changes to externalschema or rewrites of applicationprograms.13/10/201719Physical Data IndependenceRefers to immunity of conceptual schemato changes in the internal schema.Internal schema changes (e.g. usingdifferent file organizations, storagestructures/devices).Should not require change to conceptualor external schemas.Data Models include data descriptions,data relationships, data semantics,consistency constraints [Silberschatz et. al.]Data model comprises three components:data structures, data manipulators, andgeneral integrity rules [Codd (1970)] Data Models produced early in SystemsDevelopment13/10/201720 Data model makes improvements in terms of : no duplication or redundant or nullinformation reduction in physical size quicker information retrieval leads to a fully optimised set of tablesData Model produced by Case Tools techniques Normalization Entity Relationship DiagramData Model provides• a full set of related tables anddata• able to derive physical views(internal schema) of data• able to derive logical views(external schema) of data13/10/201721Physical View :• Table, Attributes (attributes), Types (datadefinition), Internal Schema View,• database structure view that a DBMSrequires.• linkage between tables via primary andforeign keys• Physical View – for DesignersLogical View• forms• information• external schema view• form or screen views provided to theend user.• a set of forms derived from the tables• logical view – for users13/10/201722Data Definition Language (DDL)• Allows the DBA or user to describe and nameentities, attributes, and relationships requiredfor the application• plus any associated integrity and securityconstraints.Data Manipulation Language (DML)• Provides basic data manipulation operations ondata held in the database.Procedural DML• allows user to tell system exactly how tomanipulate data.Non-Procedural DML• allows user to state what data is needed ratherthan how it is to be retrieved.Fourth Generation Languages (4GLs)13/10/201723Repository of information (metadata)describing the data in the database.One of the fundamental components ofDBMS.Typically stores:• names, types, and sizes of data items;• constraints on the data;• names of authorized users;• data items accessible by a user and the type ofaccess;• usage statistics. Class discussion and Lab References : Database System, Connolly, Begg, Third edition © PearsonEducation Limited 1995, 2005 Modern Database Management, 8th Edition, Jeffrey A. Hoffer,Mary B. Prescott, Fred R. McFadden Database System Concept 5th Edition, Database 14, Foundations of Computer Science CengageLearning http://www.baycongroup.com/access2007/index.html http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi41.htm
