Generalisation/Specialisation Example: code name • Subclass = sub-entity = special case. name Course ClassesIn Room #seats • More attributes and/or relationships. teacher • A subclass shares the key of its parent. ISA #computers ComputerRoom • Drawn as an entity connected to the superclass by a special triangular – A computer room is a room. relationship called ISA. – Not all rooms are computer rooms. Triangle points to superclass. – Computer rooms share the extra property that – ISA = ”is a” they have a number of computers. Subclass/Superclass Hierarchy Translating ISA to relations • We assume that subclasses form a tree • Three different approaches hierarchy. – E-R: An ISA relationship is a standard one-to-”exactly one” relationship. Each subclass becomes a relation – A subclass has only one superclass. with the key attributes of the superclass included. – Several subclasses can share the same – NULLs: Join the subclass(es) with the superclass. superclass. Entities that are not part of the subclass use NULL for the attributes that come from the subclass. • E.g. Computer rooms, lecture halls, chemistry labs etc. could all be subclasses of Room. – Object-oriented: Each subclass becomes a relation with all the attributes of the superclass included. An – One class can have several (orthogonal) entity belongs to either of the two, but not both. subclass hierarchies. The E-R approach: The NULLs approach: name name Room #seats Room #seats Rooms(name, #seats) Rooms(name, #seats, #computers) ComputerRooms(name, #computers) ISA ISA name -> Rooms.name What? name # seats # computers ComputerRoom ComputerRoom What? name # seats VR 216 NULL name # computers VR 216 ED6225 52 26 ED6225 26 #computers #computers ED6225 52 1
Comparison The object-oriented approach: • E-R approach name – Good when searching for general information about Rooms(name, #seats) all entities in the class hierarchy. Room #seats ComputerRooms(name, #seats, • ”List the number of seats in all rooms” #computers) • OO approach ISA – Good when searching for information about entities in name # seats a subclass only. What? VR 216 ComputerRoom • ”List the number of seats in all computer rooms” • NULLs approach name #seats # computers – Could save space in situations where most entities in #computers ED6225 52 26 the hierarchy are part of the subclass (e.g. most rooms have computers in them). – Reduces the need for joins (see later). E-R summary Scheduler database revisited • Entities ”We want a database for an application that we will use to schedule courses. …” • Attributes • Relationships – Multiplicity – Course codes and names, and the period the courses are given – Cardinality – The number of students taking a course – The name of the course responsible • Weak entities – The names of all lecture rooms, and the number of seats in them • Generalisation/specialisation – Weekdays and hours of lectures • Translation to relations E-R diagram for Scheduler Translate to relations period #students code Courses(code, name) GivenCourses(course, period, #students, teacher) Course GivenCourse teacher name Given course -> Courses.code Lectures(course, period, room, weekday, hour) (course, period) -> GivenCourses.(course, period) Of room -> Rooms.name Rooms(name, #seats) name Room Lecture weekday In Compare with the ”good” one from the previous lecture – we’ve reached the hour #seats same conclusion using the structured and well-defined method. 2
Exam – E-R diagrams Programming Assignment ”A small train company wants to design a booking system • Write a ”student portal” application in Java for their customers. …” – Part I: Design • Given a domain description, design a database schema • Given the problem description above, construct an E-R using an E-R diagram and functional dependencies. diagram. – Part II: Construction and Usage • Translate the E-R diagram into a database schema. • Implement the schema from Part I in Oracle. • Insert relevant data. • Create views. – Part III: Construction • Create triggers. – Part IV: Interfacing from external Application • Write a Java application that uses the database from Part III. Programming Assignment System Specification • Each task must be completed and • Your final application should have the following functionality: approved before the next can be started. – Info: A student should be able to ask the system for – Submit in good time! info about herself, including what courses she has • Preferrably, work in pairs. read or is registered to. – Register: A student should be able to register for a course. If there is no room on the course, she should be put in a waiting list. – Unregister: A student should be able to withdraw a registration. If some other student is on the waiting list, that student should be registered instead. Part I - Design Part I - Design • Design the database schema by drawing • Hand in: an E-R diagram of the domain, and then – a diagram translating your diagram to relations. – a database schema • Verify your schema by identifying all – the FDs of the domain functional dependencies that you expect to – a text report where you argue the correctness hold on the domain, and check them of your solution. against the schema. • Submission deadline: 12 November 2013 3
Functional dependencies (FDs) • X � A – ”X determines A”, ”X gives A” Database design II – ”A depends on X” • X is a set of attributes, A is a single attribute Functional Dependencies • Examples: BCNF – code � name – code, period � teacher Why ”functionally” dependent? A note on syntax • A functional dependency exists between • X � A is a (deterministic) function from X attributes in the same relation to A. Given values for the attributes in the e.g. in relation Courses we have FD: set X, we get the value of A. code � name • A reference exists between attributes in two different relations, e.g. for relation GivenCourses • Example: we have reference: – code � name course -> Courses.code – imagine a function f(code) which returns the name associated with a given code. • Two completely different things, but with similar syntax. Clear from the context which is intended . Quiz! Quiz: (an) answer What are reasonable FDs for the What are reasonable FDs for the scheduler domain? scheduler domain? code � name • Course codes and names code, period � #students • The period a course is given code, period � teacher • The number of students taking a course name � #seats • The name of the course responsible code, period, weekday � hour • The names of all lecture rooms code, period, weekday � room • The number of seats in a lecture room room, period, weekday, hour � code • Weekdays and hours of lectures 4
Assertions on a schema Multiple attributes on RHS • X � A is an assertion about a schema R • X � A,B – Short for X � A and X � B – If two tuples in R agree on the values of the attributes in X, then they must also agree on – If we have both X � A and X � B, we can the value of A. combine them to X � A,B. – course, period � teacher, #students • Example: code, period � teacher • Multiple attributes on LHS can be crucial! – If two tuples in the GivenCourses relation – course, period � teacher have the same course code and period, then • course � teacher they must also have the same teacher. • period � teacher Quiz! Trivial FDs • What’s the difference between the LHS of • A FD is trivial if the attribute on the RHS is a FD, and a key? also on the LHS. – Example: course, period � course – both uniqely determine the values of other attributes. Quiz: Is this a trivial FD? – …but a key must determine all other attributes course, period � course, name in a relation! – We use FDs when determining keys of Shorthand for relations (will see how shortly). course, period � course (trivial) course, period � name (not trivial) Inferring FDs Closure test • In general we can find more FDs • Computing the closure of X means finding all FDs that have X as the LHS. – course, period, weekday � room – room � #seats • If A is in the closure of X, then X � A. �� course, period, weekday � #seats • The closure of X is written X + . • We will need all FDs for doing a proper design. 5
Recommend
More recommend
