1 / 11

Consolidation

Consolidation. Objectives of the Lecture :. To review a given design of a relational database. To amplify the design by adding Integrity Constraints. To translate that design into SQL Create Table statements. ‘The University Database’.

calla
Download Presentation

Consolidation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Consolidation Objectives of the Lecture : • To review a given design of a relational database. • To amplify the design by adding Integrity Constraints. • To translate that design into SQL Create Table statements.

  2. ‘The University Database’ • The start point is an initial design, already provided, for a ‘University DB’ of 4 relations. • Each relation will be reviewed to see what integrity constraints should be applied to it. • The possible integrity constraints are : Attribute Data Types, Candidate Keys, Referential Integrity, Ad Hoc Constraints. • The review will determine what integrity constraints need to be added to the initial design to complete it. • The completed design will then be translated into 4 SQL Create Table statements, one for each relation.

  3. ‘STUDENT’ Relation Meaning :A student exists, identified by the ID ‘S1254’, Surname ‘Jones’, Forename ‘Jane’, whose Status is ‘H’ & who is on the ‘BIS’ course,and so on for all the students.

  4. Integrity Constraints for ‘STUDENT’ • Decide on the Data Type for every attribute. • Decide on the Candidate Keys, how many are there, and which attributes does each Candidate Key contain. • Decide on any necessary Ad Hoc Constraints, to ensure physical reality, to keep ‘Business Rules’, to specify a specific data type based on an underlying type, .... ? • Referential Integrity.Requires consideration of 2 or more relations – so leave till all the relations have been specified. Apply the same procedure to every relation .

  5. ‘COURSE’ Relation Meaning :A course exists, identified by the ID ‘BIS’, run by the ‘CEIS’ School, whose Course Leader has the ID ‘IZGG1’, & whose Duration is ‘4’ years,and so on for all the courses.

  6. ‘MODULE’ Relation Meaning : A module exists, identified by the code ‘CM0429’, named ‘Relational Databases’, & whose Module Tutor has the ID ‘CGDL1’,and so on for all the modules.

  7. ‘COURSE_CONTENT’ Relation Meaning : A ‘course content’ item exists, consisting of a course whose name is ‘ITMB’ with a module identified by the code ‘CM0429’,and so on for all the course content items.

  8. Referential Integrity • Taking every possible pair of relations in turn, are there any attributes, or sets of attributes, such that the attribute values, or sets of attribute values, in one relation should appear in another relation ? Example : every Course attribute value held in the STUDENT relation should appear as a value in the Course ID attribute of the COURSE relation,otherwise there is a student on a non-existant course. • The attributes are not required to have the same names in the 2 relations;they must have the same data types, or we are not comparing ‘like with like’.

  9. Foreign Keys • In Referential Integrity, a Foreign Key ‘refers’ to a Candidate Key. Example : the Course attribute in STUDENT refers to the Course ID attribute in COURSE, so Course is a Foreign Key referring to Course ID. • It can be useful to check whether the set of Foreign Key values (i.e. ignoring any duplicate values) should be the same as the set of Candidate Key values, or could be a subset of them. Example : if the set of different Course attribute values in STUDENT is a subset of the different Course ID attribute values in COURSE, then not every course has students on it. Is this OK ?

  10. SQL Tables • Decide on the most appropriate SQL Data Type for each attribute. • If there is more than one Candidate Key, decide which one will be the Primary Key in SQL. • Decide on the CHECK constraints required to implement any Ad Hoc constraints. • Decide which Foreign Keys will reference which Primary Keys. (Note that SQL does not allow Foreign Keys to reference Alternate Keys).Decide which order to create the tables in. A Foreign Key cannot reference a Primary Key which does not yet exist ! In a ‘CATCH-22’ situation, create the tables without Foreign Keys, & add the Foreign Keys after with Alter Table statements.

  11. Conclusion • The SQL Create Table (& any Alter Table) statements derived in the lecture will be used in the practical sessions to actually create ‘The University DB’ which will consist of these 4 SQL tables. • Use the example of ‘The University DB’ to review the DB created in previous practical sessions and resolve any issues outstanding.

More Related