This Lecture Entity/Relationship models Entities and Attributes - PDF document

This Lecture • Entity/Relationship models • Entities and Attributes Entity Relationship Modelling • Relationships • E/R Diagrams • Further Reading Database Systems • Database Systems, Connolly & Begg, Chapter 12 Michael Pound • The Manga Guide to Databases, Chapter 3 Last Lecture Database Design • Foreign Keys reference a Candidate Key in • Before we look at how • Designing your to create and use a database is important another relation. database we’ll look at • We can create a how to design one database design that is BookGenres Genre independent of DBMS • Need to consider GID GName GID BID • Often results in a more 10 2 10 Crime • What tables, keys, and efficient and simpler 11 Thriller 11 2 constraints are needed? queries once the 12 Biography 12 3 • What is the database database has been 13 Mystery 13 4 going to be used for? created 11 4 Entity/Relationship Modelling Entity/Relationship Diagrams • For example, in a • E/R Modelling is used • E/R Models are often University database we ID for conceptual design represented as E/R Lecturer might have entities for diagrams that • Entities - objects or Name Course Students, Modules and items of interest • Give a conceptual view Lecturers • Attributes – properties of the database Tutors Student • Students might have of an entity • Are independent of the attributes such as their ID, • Relationships - links Name, and Course choice of DBMS • Students could have between entities • Can identify some relationships with Module Studies problems in a design Modules (enrolment) and Lecturers (tutor/tutee) 1

Diagram Conventions Entities • There are various • Entities represent • Entities have notations for representing Lecturer ID objects or things of • A general type or class, E/R diagrams interest such as Lecturer or Name Course • These specify the shape Module • Physical things like of the various • Instances of that students, lecturers, Tutors Student components, and the particular type. E.g. employees, products notation used to Boriana Koleva, Steve • More abstract things like represent relationships Bagley are instances of • For this introductory modules, orders, Module Studies Lecturer courses, projects module, we will use • Attributes (such as simplified diagrams name, email address) Diagramming Entities Attributes • In E/R Diagrams, we will • Attributes are facts, • Attributes have Lecturer ID represent Entities as aspects, properties, or • A name boxes with rounded details about an entity • An associated entity Name Course corners • Students have IDs, • Domains of possible names, courses, • The box is labelled with values Tutors Student addresses, … • For each instance of the the name of the class of • Modules have codes, associated entity, a value objects represented by titles, credit weights, from the attributes that entity Module Studies levels, … domain Diagramming Attributes Relationships • In an E/R Diagram • Relationships are an • Relationships have ID attributes are drawn as Lecturer association between • A name ovals two or more entities • A set of entities that Name Course • Each Student takes participate in them • Each attribute is linked • A degree - the number several Modules Tutors Student to its entity by a line • Each Module is taught by of entities that • The name of the participate (most have a Lecturer attribute is written in degree 2) • Each Employee works for Module Studies the oval • A cardinality ratio a single Department 2

Cardinality Ratios Entity/Relationship Diagrams • Each entity in a • One to one (1:1) • Relationships are shown relationship can • Each lecturer has a unique Lecturer ID as links between two office participate in zero, one, entities • One to many (1:M) Name Course or more than one • A lecturer may tutor many • The name is given in a instances of that students, but each student relationship diamond box Tutors Student has just one tutor • We won’t be dealing with • Many to many (M:M) • The ends of the link optional (zero instances) • Each student takes several show cardinality of relationships modules, and each module is Module Studies taken by several students • This leads to 3 types of One Many relationship... Making E/R Models Example • To make an E/R model • General guidelines A university consists of a number of departments. you need to identify • Since entities are things Each department offers several courses. A number of • Entities or objects they are often modules make up each course. Students enrol in a nouns in the description particular course and take modules towards the • Attributes • Attributes are facts or completion of that course. Each module is taught by • Relationships properties, and so are a lecturer from the appropriate department, and • Cardinality ratios often nouns also each lecturer tutors a group of students • We obtain these from a • Verbs often describe problem description relationships between entities Example - Entities Example - Relationships A university consists of a number of departments. A university consists of a number of departments. Each department offers several courses. A number of Each department offers several courses. A number of modules make up each course. Students enrol in a modules make up each course. Students enrol in a particular course and take modules towards the particular course and take modules towards the completion of that course. Each module is taught by completion of that course. Each module is taught by a lecturer from the appropriate department, and a lecturer from the appropriate department, and each lecturer tutors a group of students each lecturer tutors a group of students • Entities – Department, Course, Module, Student, • Entities – Department, Course, Module, Student, Lecturer Lecturer • Relationships – Offers, Make Up, Enrol, Taught By, From The, Tutors 3

Example – E/R Diagram Removing M:M Relationships • The completed diagram. All that remains is to Many to many relationships are difficult to represent in a database: Student remove M:M relationships SID SName SMod 1001 Jack Smith DBS Module Department Offers Employs 1001 Jack Smith PRG MID MName 1001 Jack Smith IAI DBS Database Systems 1002 Anne Jones PRG PRG Programming Course Includes Module Taught By Lecturer 1002 Anne Jones IAI IAI AI 1002 Anne Jones VIS VIS Computer Vision Takes Student SID SName SMods 1001 Jack Smith DBS, PRG, IAI Enrols Student Tutors 1002 Anne Jones PRG, IAI, VIS Removing M:M Relationships Entities and Attributes • Many to many • Sometimes it is hard to • General guidelines relationships are tell if something should • Entities can have Student difficult to represent in be an entity or an attributes but attributes a database Student have no smaller parts attribute Has • We can split a many to • Entities can have • They both represent many relationship into relationships between objects or facts about Enrolment Takes two one to many them, but an attribute the world relationships belongs to a single entity • They are both often In • An additional entity is Module represented by nouns in created to represent descriptions Module the M:M relationship Example Example - Entities/Attributes • Entities or attributes: • Products, suppliers, and We want to represent information about • product addresses all have products in a database. Each product has a • description smaller parts so we description, a price and a supplier. Suppliers • price make them entities have addresses, phone numbers, and names. • supplier • The others have no Each address is made up of a street address, a • address smaller parts and • phone number city, and a postcode. belong to a single entity • name • street address • city • postcode 4

Example - E/R Diagram Example - Relationships • Each product has a • Each supplier has an supplier address Price • Each product has a single • A supplier has a single supplier but there is address Product Description nothing to stop a • It does not seem supplier supplying many sensible for two Street address products different suppliers to • A many to one have the same address Supplier Address Name City relationship • A one to one relationship Phone number Postcode Example - E/R Diagram One to One Relationships • Some relationships • Example - the supplier- between entities, A and address relationship Price B, might be redundant • Is one to one if • Every supplier has an Description Product • It is a 1:1 relationship address between A and B • We don’t need Has A Street address • Every A is related to a B addresses that are not related to a supplier and every B is related to Supplier Has An Address Name City an A Phone number Postcode Redundant Relationships Example - E/R Diagram • We can merge the two Price a x entities that take part in a redundant b A B y Description Product relationship together • They become a single c z Has A entity • The new entity has all a x Name Supplier City the attributes of the old one b AB y Phone number Postcode Street address c z 5