Week 3’s lecture by Manoj Kathpalia got into database design. We continued our referencing to the ANSI/SPARC architecture which it appears will be an ongoing theme.
The lecture ran through the Database Design Cycle introducing Requirements Definition, Conceptual Design, Logical Design and Physical Design. With detailed analysis of ER modeling which applies to Conceptual and and Logical Design (which are both at the conceptual level of the ANSI/SPARC architecture).
I have some confusion over Cardinality, I assume the Cardinality of Relation and the Cardinality of a Relationship are two different things. The cardinality of a relation being the number of tuples and the cardinality of a relationship being either 1:1, 1:M or M:N. I will need to clarify this with Manoj.
The ER model is quite a simple graphical modeling tool which we review in the tutorial, it does however seem that there are a number of acceptable notations in ER modeling some of which can be seen here.
Pearl of the week:
Although ER modeling can be used for both Conceptual and Logical Design, the Conceptual Level does not include Foreign Keys and allows Many-to-Many relationships.
After working through the ER model, we touched on integrity constraints, specifically Referential Integrity constraints:
- Foreign Key references must be valid or null
- Update Rules either restricted, cascaded or null (ie: when the customer_ID is updated in the customer table, the foreign key in the order table can either stay the same, update or become null).
- Deletion Rules either restricted, cascaded or null (ie: when the customer_ID is attempted to be deleted in the customer table, it can either be restricted (forbidden delete), delete the associated orders :O or set the customer_ID filed of the order to null).
Finally we covered an introduction to Database Design Language [DBDL]. We did some more work on DBDL in the tutorial although there were some discrepancies between the lecture example and the tut examples (ie including CK and PK). From the final question of the tutorial, my derived relations in DBDL were as follows:
book(ISBN, call_num, title, pub_date, publisher_ID) SK title AK call_num author(author_ID, surname, first_name, DOB, initials) SK surname SK initials author_book(ISBN, author_ID) FK ISBN -> book - Delete restrict, Update Restrict FK author_ID -> author Delete restrict, Update Restrict subject (sub_name) subject_book (ISBN, sub_name) FK ISBN -> book - Delete restrict, Update restrict FK sub_name -> subject - Delete restrict, Update Restrict publisher (publisher_ID, pub_name, street_add, city, postcode, country) AK pub_name SK pub_name
I did not include CK and PK as they were not included in the lecture’s example.