Database design Given a domain, know how to design a database that - PowerPoint PPT Presentation

Course Objectives – Design When the course is through, you should Database design – Given a domain, know how to design a database that correctly models the domain and its constraints The Entity-Relationship model ”We want a database that we can use for scheduling courses and lectures. This is how it’s supposed to work: …” 1 2 Course Objectives – Design Formal Course Objectives Judgement and approach: • Entity-relationship (E-R) diagrams • Functional Dependencies 1.EVALUATE and CREATE different models for a database domain using Entity-Relationship diagrams and relational schemas. • Normal Forms Skills and abilities: code day hour roomNr 1.CONSTRUCT an Entity-Relationship diagram for a given domain. name 2.TRANSLATE an Entity-Relationship diagram into a relational database name Course Of Lecture In Room schema. dept building responsible 3 4 1

The Entity-Relationship approach Entities and entity sets • Design your database by drawing a picture • Entity = ”thing” or object of it – an Entity-Relationship diagram – course, room etc. – Allows us to sketch the design of a database • Entity set = collection of similar entities informally (which is good when communi- – all courses, all rooms etc. cating with customers) • Entities are drawn as rectangles • Use (more or less) mechanical methods to convert your diagram to relations. Course – This means that the diagram can be a formal specification as well 5 6 Attributes • Entities have attributes. Example: Keys are underlined code • All entities in an entity set have the same attributes (though not the same values) Course name teacher • Attributes are drawn as ovals connected to the entity by a line. • A course has three attributes – the unique course code, a name and the name of the teacher. • All course entities have values for these three attributes, e.g. (TDA357, Databases, Niklas Broberg). 7 8 2

Translation to relations A note on naming policies • An E-R diagram can be mechanically • My view: A rectangle in an E-R diagram translated to a relational database schema. represents an entity, hence it is put in singular (e.g. Course). • An entity becomes a relation, the attributes of the entity become the attributes of the – Fits the intuition behind attributes and relationships better. relation, keys become keys. • The book: A rectangle represents an entity code set, hence it is put in plural (e.g. Courses) name Course Courses(code, name, teacher) – Easier to mechanically translate to relations. teacher 9 10 Relationships Example: • A relationship connects two (or more) entities. name code • Drawn as a diamond between the related name Course LecturesIn Room #seats entities, connected to the entities by lines. teacher • Note: Relationship � Relation!! • A course has lectures in a room. • A course is related to a room by the fact that the course has lectures in that room. • A relationship is often named with a verb form (HasLecturesIn) 11 12 3

Translation to relations References • A relationship between two entities is Courses(code, name, teacher) Rooms(name, #seats) translated into a relation, where the LecturesIn(code, name) • We must ensure that the codes used in attributes are the keys of the related LecturesIn matches those in Courses . entities. code name – Introduce references between relations. name Course Room #seats LecturesIn – e.g. the course codes used in LecturesIn teacher reference those in Courses . Courses(code, name, teacher) Courses(code, name, teacher) Rooms(name, #seats) Rooms(name, #seats) LecturesIn(code, name) LecturesIn(code, name) code -> Courses.code References name -> Rooms.name 13 14 ”Foreign” keys Quiz • Usually, a reference points to the key of Suppose we want to store the number of another relation. times that each course has a lecture in a certain room. How do we model this? – E.g. name in LecturesIn references the key name in Rooms . #times – name is said to be a foreign key in code name LecturesIn . name Course LecturesIn Room #seats teacher 15 16 4

Attributes on relationships Translation to relations • Relationships can also have attributes. • A relationship between two entities is translated into a relation, where the attributes are the keys • Represent a property of the relationship of the related entities, plus any attributes of the between the entities. relationship. #times – E.g. #times is a property of the relationship code name between a course and a room. Course Room name LecturesIn #seats #times teacher code name name Course Room #seats LecturesIn Courses(code, name, teacher) Room(name, #seats) LecturesIn(code, name, #times) teacher code -> Courses.code 17 18 name -> Rooms.name Quiz Relationship (non-)keys Why could we not do the same for weekday? • Relationships have no keys of their own! – The ”key” of a relationship is the combined weekday keys of the related entities code name – Follows from the fact that entities are either Course Room name LecturesIn #seats related or not. teacher – If you at some point think it makes sense to put a key on a relationship, it should probably • Not a property of the relationship – a course can have be an entity instead. lectures in a given room on several weekdays! • A pair of entities are either related or not. 19 20 5

Multiway relationships • Translating to relations: • A course has lectures in a given room on day different weekdays. Weekday day code name Weekday name Course LecturesIn Room #seats code name teacher Course Room name LecturesIn #seats Courses(code, name, teacher) teacher Rooms(name, #seats) Weekdays(day) LecturesIn(code, name, day) code -> Courses.code name -> Rooms.name 21 22 day -> Weekdays.day Many-to-many relationships Many-to-one relationships • Many-to-many (n-to-n, many-many) • Many-to-one (n-to-1, many-one) relationships relationships – Each entity in either of the entity sets can be – Each entity on the ”many” side can only be related to any number of entities of the other related to (at most) one entity on the ”one” set. side. code name code name name Course Room #seats LecturesIn name Course Room #seats ResidesIn teacher Arrow means teacher ”at most one” – A course can have lectures in many rooms. – Courses have all their lectures in the same room. – Many courses can have lectures in the same room. – Many courses can share the same room. 23 24 6

Many-to-”exactly one” One-to-one relationships • One-to-one (1-to-1, one-one) relationships • All entities on the ”many” side must be – Each entity on either side can only be related related to one entity on the ”one” side. to (at most) one entity on the other side. – This is also known as total participation code name code name name Course Room #seats name Course Room #seats ResidesIn ResidesIn teacher teacher Rounded arrow means ”exactly one” – Courses have all their lectures in the same room. – Only one course in each room. – All courses have all their lectures in some room. – Not all rooms have courses in them. – Many courses can share the same room. 25 26 Translating multiplicity Translating multiplicity • A many-to-many relationship between two • A X-to-”exactly one” relationship between entities is translated into a relation, where two entities is translated as part of the the attributes are the keys of the related ”many”-side entity. entities, and any attributes of the relation. code name #times name Course ResidesIn Room #seats code name name Course Room #seats LecturesIn teacher teacher Courses(code, name, teacher, room) Courses(code, name, teacher) room -> Rooms.name Rooms(name, #seats) LecturesIn(code, name, #times) Rooms(name, #seats) code -> Courses.code 27 28 name -> Rooms.name 7

Quiz Aside: the NULL symbol • Special symbol NULL means either How do we translate an X-to-one (meaning – we have no value, or ”at most one”) relationship? – we don’t know the value code name Course Room name ResidesIn #seats • Use with care! teacher – Comparisons and other operations won’t work. Courses(code, name, teacher, room) Room(name, #seats) – May take up unnecessary space. Courses(code, name, teacher) or ? Room(name, #seats) ResidesIn(code, room) 29 30 Translation comparison Bad E-R design Courses(code, name, teacher) Rooms(name, #seats) code name Note that ”room” ResidesIn(code, room) is not a key here Course Room name ResidesIn #seats – Safe translation - no NULLs anywhere. – May lead to duplication of the course code. teacher room – May lead to more joins . – Default translation rule, use unless you have a good reason not to. • Room is a related entity – not an attribute as well! Courses(code, name, teacher, room) Rooms(name, #seats) • E-R modelling error #1 – don’t do this!! – Will lead to NULLs for courses that have no room. – Can sometimes be preferred when not having a room is an uncommon exception to the rule. – Reduces the need for joins. 31 32 8