INFS1603 02 ER Modelling 1 rev11 Solutions PDF

Preview

Summary

Download INFS1603 02 ER Modelling 1 rev11 Solutions PDF

Description

© Dr. Daniel Schlagwein or the respective right holders. All rights reserved.

UNSW Business School | Information Systems and Technology Management

INFS1603 Business Databases

W2: ER Modelling Dr. Daniel Schlagwein

Recap: W1 Learnings q q q q q q q

2

Data vs. information Data stored in databases Database management system (DBMS) Database design defines database structure File systems are not great for data (why?) Relational DBMS are great for data (why?) The next big thing: big data

Plan: W2 Learnings q Data Modelling: § Data model as a (relatively) simple abstraction of the complex real-world. § A good DBMS will perform poorly with a poorly designed database. § One modelling technique to design a database: Entity Relationship Modelling. q Entity Relationship Modelling: § Entity Types and Entity Instances § Attributes and Values § Keys § Relationships § Connectivity and Cardinality

3

Data Models

Source: gerardnico.com

4

Data Modelling q A model is an abstraction of a real-world. q Data modelling is simple representation of complex world data structure. q Data modelling can be classified based on their degree of abstraction § § § §

Conceptual Internal External Physical

q External model: view

5

From&ANSI/SPARC

ANSI/SPARC Data Model

6

7

Conceptual Data Modelling “A conceptual data model is a representation of organizational data.” Data modelling uses two techniques: q Entity-Relationship (ER) modelling: Top-down approach. Begins by looking for the data groups in the system. q Normalization: Bottom-up approach. Begins by looking at the smallest individual items of data recorded by the system.

8

Internal Model q q q q q

The internal model is the model that we used when database is implemented. The internal model maps the conceptual model to the DBMS Internal schema depicts a specific representation of an internal model. The internal model depends on the specific database software. Hence, a change in DBMS software requires internal model be changed.

q Logical independence: you can change the internal model without affecting conceptual model!

9

Conceptual Model vs. Internal Model

Professor (PName, DofB, Address, Department) Course (CourseID, CourseName, PName)

Internal(Model 10

Conceptual Modelling: ER Model q An ER model is a detailed, logical representation of the data for an organization or for a business area. q The ER model is expressed in terms of entities in the business environment, the relationships or associations among those entities, and the attributes of both the entities and their relationships. q An ER model is normally expressed as an ER diagram, which is a graphical representation of an ER model. In this course we will use Chen Model.

11

Entities

Source:&wikiscreenplay.ca

12

Entity (Type) and (Entity) Instance q en·ti·ty /ˈentitē/ (Noun) § A thing with distinct and independent existence. § Existence; being: "entity and nonentity". q Synonyms: being - existence - essence - thing q in·stance /ˈinstəns/ (Noun) § An example or single occurrence of something: "an instance of corruption". § A particular case: "in this instance". q Synonyms: example - case - sample - event - occurrence - exemplar q One type of things is a Person. Daniel is an instance of Person. q One type of things is a Drink. Espresso an instance of Drink.

13

Entity (Type) and (Entity) Instance q Entities: “An entity is an object about which the system requires to hold data.” q An entity type (entity class) is a collection of entities that share common properties or characteristics. q It is represented as a rectangle box in the ER model diagram with the name of the entity inside.

q An entity instance is a single occurrence of an entity type.

STUDENT § Entity Type § STUDENT §

14

Entity Instances s221 Martin, S. s222 Fong, L.

SE BSc

Example of Inappropriate Entities A treasurer looks after researchers’ research accounts. Each account pays more than one expenses. The treasure prints expense reports regularly e.g. every month.

15

ER Model with Only Appropriate Entities

16

Attributes

17

Attributes q “An attribute is a property or characteristics of an entity that is interest to the organization.” q Each entity type has a set of general attributes associated with it. § For example: The entity type STUDENT has the attributes “Student id”, “StudentName”, and “ProgramName”. q Each entity instance has specific values of the attributes associate with it. § For example: The entity instance S. LAW has the attribute values “S221”; “Law, S.” and “SE”. q Attributes have domains. A domain defines the attribute’s set of possible values.

18

Attributes q A composite attribute is a super-set of sub-attributes. § Example: address (= street, city, state and area code) q A simple attribute cannot be subdivided. § Example: ZID. q A single-valued attribute can only have one value (simple or composite). q A multi-valued attribute can have many values. q A derived attribute is derived using an algorithm. q A key attribute is unique so to identify the entity.

19

Example of Attributes

20

A multivalued attribute (Skill) A derived attribute (Years_Employed)

A composite attribute (Address)

21

A simple key attribute (Student_ID)

A composite key attribute (Flight_ID)

22

Multivalued Attributes

23

Solution 1: Splitting Multi-Valued Attributes

24

Solution 2: Splitting Multi-Valued Attributes

Relationship

25

Keys

26

Keys “A key to an entity is an attribute or set of attributes whose values uniquely identify one occurrence of that entity.” q Candidate Key: It is an attribute that uniquely identifies each instance of an entity type. q Primary Key (PK): It is a candidate key that has been selected to be used as an identifier for an entity type. q Foreign Key (FK): An entity may contain as one of its attributes a data item which is the primary key of another entity.

27

Keys

28

Relationships

Source: yimg.com

29

Relationships “A relationship is a link between two entities which is significant for the system” q The degree of a relationship is the number of entity types that participate in that relationship. q The most common relationships are unary, binary, ternary, and quaternary. q The relationships between entities can be § One to one § One to many § Many to many § Recursive

30

Entity Relationships

31

32

33

Quaternary Relationship (4 entities)

34

Connectivity

Source:&scvo.org.uk

35

Connectivity § Connectivity: “Connectivity is used to describe the relationship classification.” § The ER diagram indicates connectivity by using a numeric notation.

36

Basic Relationship (one-to-one)

37

Basic Relationship (one-to-one)

38

Basic Relationship (one-to-many)

§ § §

39

A movie (e.g., Avatar) can be stocked as several blurays (e.g., 30 copies) All blurays contain a film. There is “one-to-many” relationship between film and bluray.

Basic Relationship (many-to-many)

40

Basic Relationship (many-to-many)

§ §

41

An employee completes many courses. Each course is completed by many employees.

Connectivity

42

Cardinality

Source:&telegraph.co.uk&/&AFP

43

Cardinality q Cardinality: “Cardinality expresses the specific number of entity occurrences associated with one occurrence of related entity” q A cardinality constraint specifies the number of instances of entity A that can be associated with each instance of entity B. Cardinality constraints are derived from business rules. q Business rules: They are derived from organization’s data environment. q Minimum cardinality is the minimum number of instances of one entity that may be associated with each instance of another entity. q Maximum cardinality is the maximum number of instances of one entity that may be associated with each instance of another entity.

44

Cardinality

How to read this: q A class is read by (1,1) professors. A professor teaches (0,3) classes. q A class has enrolled in it (0,35) students. A student enrolls ins (1,6) classes.

45

Relationship Participation

A participating entity in a relationship can be either optional or mandatory. Determined by the specific meaning of the terms used. § Depends on context. § Need to state assumptions.

q q

46

CLASS is optional to COURSE:

COURSE and CLASS in a mandatory relationship:

47

Ternary Relationships

48

Weak Entities q Weak entity is an entity that relies on the existence of another entity. It has a primary key (PK) that is partially or totally derived from the parent entity in the relationship. q Weak entity meets two conditions: § Existence-dependent: Cannot exist without entity with which it has a relationship § Has primary key that is partially or totally derived from the parent entity in the relationship. q Database designer usually determines whether an entity can be described as weak based on the business rules.

49

Weak Entity Example

50

Weak Entity Example

51

Weak Entity Example

§ An&Existence-Dependent&Relationship&Between&Course&and&Class 52

Weak Entity Example

53

W2 Learnings q Data Modelling: § Data model as a (relatively) simple abstraction of the complex real-world. § A good DBMS will perform poorly with a poorly designed database. § One modelling technique to design a database: Entity Relationship Modelling. q Entity Relationship Modelling: § Entity Types and Entity Instances § Attributes and Values § Keys § Relationships § Connectivity and Cardinality

54

Questions

55

To Do for Week 3 q Submit your lab exercise q Read chapter 2-4 from the textbook q Start working through the Oracle exercises from the Casteel book q Go over your notes from this week q Get a copy of W3 lecture notes from the web q Try to solve the homework / exercises

56

Homework / Exercises

57

Exercise 1

q What are the candidate keys in both entities? q What is the PK in Employee? q What is the PK in Department? q What is the FK? q What is the name of Head of Computer Science? q List the names of people who work for J. F. Smith! q Draw you first ER diagram! 58

Your Solution for Exercise 1

59

Exercise 2

① What is the PK for TUTORIAL_CLASS? ② What is the PK for LOCATION? ③ Can Mr. Parkin use Video and Computer in his class?

60

Your Solution for Exercise 2

① What is the PK for TUTORIAL_CLASS? > CourseID, TutID ② What is the PK for LOCATION? > ClassID ③ Can Mr. Parkin use Video and Computer in his class? > No (see entry for Quad 1045)

61

Exercise 3 q Examine the ER diagram in this figure and state which of the following statements is true. ① ② ③ ④ ⑤ ⑥

62

A course can be on one program only A program has only one course A student does not have to be on any program A student must be on one program only A program may not have any course at all A program may not have any student in it

Your Solution for Exercise 3

① ② ③ ④ ⑤ ⑥

63

A course can be on one program only A program has only one course A student does not have to be on any program A student must be on one program only A program may not have any course at all A program may not have any student in it

TRUE — FALSE TRUE — FALSE TRUE — FALSE TRUE — FALSE TRUE — FALSE TRUE — FALSE

Exercise 4 Use these business rules to write all the appropriate connectivity in the ER diagram below. Write all the cardinalities into the model. § A department employs many employees, but each employee is employed by one department. § Some employees, known as "rovers," are not assigned to any department. § A division operates many departments, but each department is operated by one division § An employee may be assigned to many projects, and a project may have many employees assigned to it. § A project must have at least one employee assigned to it. § One of the employees manages each department, and each department is managed by one employee. § One of the employees runs each division, and each division is run by one employee.

64

Your Solution for Exercise 4

65

Exercise 5 Create an ER model for the following requirements: q UNSW Fitness Club § The club offers a number of exercise classes (described by exercise number, exercise type, exercise level) for its members i.e. staff and students. § A member can participate in several exercise classes or none at all. § Each exercise class has one employee assigned as its instructor. § Each instructor (described by instructor id, name, address) teaches at least one class and may teach up to 5 classes. § Each exercise class is taught in a specified room, on a specified date and time. § Rooms can be used for different classes.

66

Your Solution for Exercise 5

67

Class Exercise 6 q Draw the ER diagram to represent the following relationships. Write down the connectivity and cardinalities.

68

Your Solution of Exercise 6

69

Class Exercise 7 q Examine the following business rules and create the appropriate ER diagram for each of the specified relationships. q q q q

Company ABC operates four departments. Each department employs several employees. Each employee may have several dependants (i.e., partner, kids, mother, father). Each employee has an employee history.

q Are they all appropriate entities?

70

Your Solution for Exercise 7

71

Source: murketing.com

72...