Topic Database Modelling

1. TopicDatabase Modelling Advanced Databases

2. 3 phases of database design • Need a systematic approach to designing and building the dB (1) Conceptual (2) Logical (3) Physical Advanced Databases

3. Phase 1: Conceptual database design • Step 1: Build conceptual data model • Step 1.6 Consider use of enhanced modeling concepts (optional step) • Step 1.7 Check model for redundancy • Step 1.8 Validate conceptual model against user transactions • Step 1.9 Review conceptual data model with user • Step 1.1 Identify entity types • Step 1.2 Identify relationship types • Step 1.3 Identify and associate attributes with entity or relationship types • Step 1.4 Determine attribute domains • Step 1.5 Determine candidate, primary, and alternate key attributes Advanced Databases

4. Phase 2: Logical database design • Step 2: Build and validate Logical database design • Step 2.1 Derive relations for logical data model • Step 2.2 Validate relations using normalization • Step 2.3 Validate relations against user transactions • Step 2.4 Define integrity constraints • Step 2.5 Review logical data model with user • Step 2.6 Merge logical data models into global model (optional step) • Step 2.7 Check for future growth Advanced Databases

5. Phase 3: Physical database design • Step 3 Translate logical data model for target DBMS Step 4 Design file organizations and indexes • Step 4.1 Analyze transactions • Step 4.2 Choose file organization • Step 4.3 Choose indexes • Step 4.4 Estimate disk space requirements • Step 3.1 Design base relations • Step 3.2 Design representation of derived data • Step 3.3 Design general constraints Advanced Databases

6. Phase 3: Physical database design (continued) • Step 5 Design user views • Step 6 Design security mechanisms • Step 7 Consider the introduction of controlled redundancy • Step 8 Monitor and tune the operational system Advanced Databases

7. Our focus… • Will concentrate on the techniques for producing the Entity Relationship Diagram (ERD) • This is the typical design diagram for explaining • The entities • Their attributes • Their relationships • Key output from Conceptual data modelling • Greater detail as logical and physical design progresses,… Advanced Databases

8. Entities • “An entity is a business object that represents a group, or category of data.” • Like a database table Entity = “thing” Advanced Databases

9. Entities diagramatically Customer Customer entity Customer branches (a customer can be in more than One branch) CustBr

10. Advanced Databases Entities as table - with data Customer Customer branches

11. Advanced Databases So what does that mean ? • The DB is recording information about your customers and their branches • These are examples of real-world entities • An entity is any “object” about which we wish to store information in the DB (table) • Entities are linked together by relationships • CustID, BranAddr, etc. are data items or fields or attributes

12. Advanced Databases To set up that table in SQL... Create table Customer ( CustID char(10) NOT NULL, CustName char(50), CustAddr char(100), Credit_Lim number(10), Debtor_Bal number(10) ); select * from Customer where CustID = “Dunn0001”

13. Attributes • “An attribute is a sub-group of information within an entity.” • Like a database field • Shown as list under the entity name Attributes = “ways to describe the thing” Advanced Databases

14. Keys Customer Primary key = uniquely identifies EACH row What’s the primary key here? Advanced Databases

15. What’s the primary key here? (can be composite.. More than one field Advanced Databases Customer branches

16. Keys Foreign key = A column(s) one a table that matches the primary key on another table. Advanced Databases

17. Keys Advanced Databases Foreign keys – enforce referential integrity What happens if I (try) to delete customer DUNN001 from the customer table? X

18. What does each key mean? Primary key = uniquely identifies EACH row e.g. student ID – everyone has a different one + Alternate key – could have been a primary key but isn’t. e.g. student ID (PK) and PPS ID (AK) = Candidate keys: The primary key and the alternative keys. Any column or a combination of columns that can qualify as unique key in database. e.g. student ID and PPS number Foreign key = A column(s) one a table that matches the primary key on another table. E.g. student_school (on student table, links to table contains schools) Advanced Databases

19. Entity attribute display in data models Advanced Databases

20. Entities and tables The words “Entities “ and “tables” are sometimes used interchangeably. But they’re not quite the same thing…. Advanced Databases

21. Relationships between entities Advanced Databases To start defining relationships between entities, first describe the way that they are connected in real life Let’s go back to the ERD in the quiz There are four faculties in the college: science, applied arts, built environment, business. Each student is registered on a programme within a faculty. Programmes consist of a set of modules, with some module offered on more than one programme. A student on a programme has to register for the compulsory modules on tphe programme and also take a number of optional modules. All modules have a unique module ID, and an associated credit waiting (ECTS).

22. Advanced Databases Relationships between entities Need to be able to say “how many” of the entities Are participating in the relationship 1:1? One to one 1:M? One to many N:M? Many to many

23. Sample Entity Relationship Diagram – book store Note: The attributes are listed The data types of the attributes are listed What stage of the design is this? Conceptual, logical or physical? Imagine you’ve drawn this ERD What was the business description of the data requirements given in the first place? Advanced Databases

24. Book store ERD Imagine you’ve drawn this ERD What was the business description of the data requirements given in the first place? Some thing like – Need to store information about the book titles that we sell, In our bookstore, we store information about authors, the titles that they write . An author can write many titles. If the author has died they will have an obituary. The bookstore has many ongoing sales promotions that include many titles We categorise our titles along the usual lines – popular fiction, crime, health and so on We also store information about our publishers and who the main sales contact is in the publisher Advanced Databases

25. Relationships between entities When two entities are “related”.. There should be a word to describe this relationship e.g. Entities Relationship staff, branches Staff managebank branches student, modules student attendsclasses newspaper, rentalProperties newspapers advertiserentalProperties You need to understand this “word” in order to figure out the information needed to define the relationship for the ERD On an ERD, need to know how many of each entity takes part in the relationship – question both entities about “how many”? e.g. For the Staff manages bank branches relationship Staff can manage how many branches at most?? A staff person can manage one branch Staff must manage how many branches at min? A staff person can have no branch to manage Advanced Databases

26. Relationship cardinality “Can” and “Must” get you there.. “eh…What’s the cardinality of your mobile phone spend in euro each week? Relationship cardinality is about assigning the 1:1, 1:n etc etc Advanced Databases

27. Maximum Cardinality Can…. Advanced Databases

28. The 3 types of maximum cardinality One to one 1:1 An employee can be assigned one computer One to many 1:n A mother can have many children Many to many m:n An order can have many product and vv How can you tell the difference between 1:m and m:n e.g. Student and module entities. What’s the maximum cardinality? Advanced Databases

29. Minimum cardinality Must… Advanced Databases

30. Minimal cardinality What’s the minimum cardinality* for our examples? An employee must be assigned ? computer A mother must have ? children An order must have ? product * zero (optional) or one (must have at least one) Advanced Databases

31. Minimal cardinality What’s the minimum cardinality* for our examples? An employee must be assigned ? Computer Ans: Depends on the rule of the individual company. Probably zero A mother must have ? children ans: Definitely zero (optional!) An order can have ? Products ans: must have at least one * zero (optional) or one (must have at least one) Advanced Databases

32. Maximum and minimum cardinality Now we know how to get the maximum (can) and minimum – But it has to be done BOTH ways to complete the relationship. We only did ONE direction Maximum cardinality Minimim cardinality A computer can be assigned to one employee A computer must be assigned to one employee -No A child can have one mother A child must have a mother? YES A product can be on many orders A product must be on an order? NO Advanced Databases

33. Maximum and minimum cardinality Once Maximum and Minimum cardinality has been figures out Between the entities – then they can be notated in whatever design Tool you’re using Advanced Databases

34. Just for reference - Data modelling notation:Erwin Max Max Min Min Note: Solid line ____ versus Dashed line ---- in Erwin = identifying versus non identifying relationship between two entities Advanced Databases

35. Create relationships:n:m relationships – intersection table Advanced Databases

36. Drawing the ERD with what you’ve learnt so far… Read the specification and make sure your understand it Figure out what your entities are e.g. sales, book title, authors Figure out which ones are related (don’t worry yet about the cardinality – helps to put the word in (e.g. “has”) Figure out the attributes of each entity Identify the primary key for each entity Any foreign keys? Identify the cardinality of the relationship Advanced Databases

37. Exercise A furniture company needs to develop a database to store information about its customers and sales Customer details need to be captured, such as name, address. The company wants to be able to report on what their sales – what each customer bought (i.e. what furniture items). They would also like to know what the items are. Try and sketch out the entities involved. Advanced Databases

38. 38 Advanced Databases Recursive relationships Advanced Databases S McKeever EMPLOYEE supervises is supervised by

39. For reference.. Summary of Erwin symbols Advanced Databases

40. Summary of Erwin symbols (cont.) Advanced Databases

41. Some examples Note.. The symbols right beside the entity tell you how many of “it” can and must apply to the other entity. How many companies can and must an employee belong to? (note: sometimes I I shown, sometimes I Advanced Databases

42. Some examples (continued) Advanced Databases

43. Some examples (continued) Advanced Databases