RECORD THE LECTURE
ENTITY-RELATIONSHIP (ER) MODEL
COMP2420/COMP6420 INTRODUCTION TO DATA MANAGEMENT, ANALYSIS AND SECURITY WEEK 8 – LECTURE 1
Copyright By PowCoder代写 加微信 powcoder
Tuesday 26 April 2022
John (course convenor)
Live demo by (Head Tutor) School of Computing
College of Engineering and Computer Science
Credit: (previous course convenor)
HOUSEKEEPING
Midsemester Exam
Thursday 21 April at 1pm (Canberra time)
Some people had issues with decryption – sincere apologies
As advised during exam support, decryption issues will be taken into account. Students who had the issues could consider start time to be when issues were resolved and make a note when submitting link to invigilation video.
I am taking on-board students’ suggestions for making the mid-semester redeemable and have sought advice from school ADE
Outcome to be communicated by end of this week
Upcoming public holidays
• ANZAC Day Monday 25 will be no lectures. A make-up session will be arranged on the corresponding Tuesday at 2pm instead to record a lecture, we will run it live and students are welcomed to join in. labs will be rescheduled and students can attend any other labs this week to help make-up for missed lab.
Deferred exam
• We will be contacting you this week if you were approved to take a deferred exam.
• Deferred exam is intended to be scheduled in Week 9.
Assignment 1 results
• Release of Assignment 1 results is planned by Monday of next week.
• Exception: if you had an extension or under investigation for plagiarism
Course rescheduling
• ER modeling and Normalisation this week (week 8)
• Security part from next week
• No material is being cut out from the course – just re-arranged
03 04 05 06 07
Describe and explain the steps involved in the general approach to database design
Recall the two most popular techniques/approaches used in database design
Discuss the key components involved in the Entity- Relationship (ER) model for designing databases
Apply the concepts in ER modelling to designing a database
Construct relational schema from ER diagrams Develop relevant relational schema using SQL as a DDL
Reflect on design considerations when constructing an ER model and/or a relational schema
By the end of this lecture, you should be able to:
ENTITY RELATIONSHIP (ER) MODEL
Database Design
• Database applications are large and complex
• Important that databases are designed well
• Results in improved efficiency and data quality
• Different approaches to database design
General approach
• Requirements Analysis
• Conceptual Design
• Logical Design
• Physical Design
• Security Design
Requirements Analysis
• What information is to be stored
• How is to be used
• Who should have access
Conceptual Design
• High-level description of the database – Conceptual Schema
• Graphical representation often used
• Identify functional requirements
• Use this to verify against the conceptual schema:
– Can each operation be performed
– Complexity of the operation
– Performance or scalability concerns
Logical Design
• Map the conceptual schema to a logical schema
• Logical data model
Physical Design
• Not always done
• Smaller applications have few
physical design concerns
• Larger systems usually need physical design
– Indexes (bitmap, B-trees, etc)
– Partitioning of data (range, hash, etc)
Security Design
• Security Model
• Security Policies
• Access Control
• Often not considered at the earlier stages of database design
Database design approaches
• Entity-Relationship modelling
– A graphical technique for understanding and organizing data
• Normalisation
– Design technique to organize data so that there is no redundancy and all related data items are stored together.
• One of the most cited articles in Computer – “The Entity-Relationship model – toward
a unified view of data” , 1976
• A very common model for schema design
• Allows for complex schemas to be specified in graphical form
ER Model • Basic concepts simple, but can represent complex abstractions
• Easily mapped to the relational model • Used by everyone!
Key components
• Entity-Sets
• Relationships (between entity-sets)
• Attributes (of entity-sets and relationships)
Entity and Entity-set
• An entity is anything than can be represented uniquely
– e.g., a student, a customer, a product
• An entity has a set of attributes
• Entities are uniquely identified by
some set of these attributes
• Each attribute has a name and a domain
• Each attribute in an entity has a corresponding value
An entity-set is a named collection of like entities with the same attributes
e.g., student, customer, product
Entity-Set Diagrams
• An entity-set is denoted by a box
• Named in the singular by convention.
e.g. student, not students
• Attributes are represented by ovals attached to an entity-set
• Shapes are important, colours are not.
• Individual entities are not represented in the diagram!
Student Entity-Set
• A key is a minimal set of attributes that uniquely identifies an entity
• Can consists of one or more attributes
• Attributes that make up a key are underlined in the ER diagram
Example: Key
Student Attributes
Relationships
• A relationship is an association between one or more entities e.g. a student and the course that the student is enrolled in
• Uniquely identified by the keys of its entities
• Relationship names are usually verbs
• The degree of a relationship is the number of entities that it connects e.g. unary, binary (most common), ternary
• Denoted by a diamond in the ER Diagram
Unary relationship
Unary Relationship
Binary relationship
Binary Relationship
Ternary relationship
Ternary Relationship
Ternary vs three way binary relationship
A ternary relationship is not, in general, equivalent to three binary ones
Ternary Vs Three Way Relationships
Relationship mapping
• one-to-one (1:1)
• one-to-many (1:N)
• many-to-many (N:M)
• Indicated on the ER diagram by placing a symbol on each end of the relationship.
Relationship Mapping 1
Relationship Mapping 2
Relationship participation
• Total, if every entity in an entity-set E participates in at least one relationship in a relationship-set R.
– Shown with a double line • Partial, otherwise
– Shown with a single line
Multiple relationships
More than one relationship can exist between two entities
Multiple Relationships
Weak Entity-Sets
• Sometimes you can’t define a primary key for an entity-set.
Called a weak entity-set. Shown as a double rectangle
• Weak entity-sets must be associated with another (strong) entity-set – the identifying or owning entity-set. The identifying relationship is shown with a double diamond.
Weak Entity-Sets
• Its participation in this relationship is total.
• Weak entities have a partial key that distinguishes between weak entities associated with a strong entity. Attributes of the partial key are shown with a dashed underline.
Weak Entity Set
Attributes
• An attribute has a name and a domain (set of possible values)
• A key consists of one or more attributes that uniquely identify the entity
• Simple attributes are atomic – no sub-parts
– student number
• Composite attributes have sub-parts
– address consists of number, street, city, state, etc
Attribute cardinality
• Single-valued attributes store only one value. e.g. student id
• Multi-valued attributes can hold multiple values – a set of values. e.g. phone numbers
– Shown using a double ellipse
Attribute source
• Base attributes are stored in the database – e.g. service date of a car
• Derived attributes are computed from other attributes
– e.g. number of days from last service of a car
– Shown using a dashed ellipse and connecting line to the relevant entity-set
A multi-valued composite attribute
Multi-valued composite attribute
Relationship attributes
• Relationships can have attributes
• Drawn attached to the relationship
• Usually only M:N relationships have attributes
Example – Unary
Relationship Attribute – Unary
Example – Binary
Relationship Attribute – Binary
Example – Ternary
Relationship Attribute – Ternary
Design Considerations
• Should a concept (e.g. person) be an entity or an attribute?
• Should a concept be an entity or a relationship?
• Should we use a single multi-way relationship or a new entity with binary relationships?
ER Diagrams to •
Both entity-sets and relationships become relations
Each entity in an entity-set is a tuple, composed of the entity’s attributes and having the same primary key.
A relation becomes a multiset of tuples, with each row/tuple being:
– composed of the union of the entity-sets’ keys
– has the entities’ primary keys as foreign keys
– has the union of the entity sets’ keys as primary key
Relational Schema
Example – Entities
ER to Schema – Entities
Example – 1:1
ER to Schema – 1:1
Example – 1:N
ER to Schema – 1:N
Example – M:N
ER to Schema – M:N
Example – Unary 1:N
ER to Schema – 1:N
Example – Unary M:N
ER to Schema – M:N
Example – Ternary
ER to Schema – Ternary
Example – Ternary
ER to Schema – Ternary
SQL AS DDL
Data Definition Language (DDL)
• SQL is a Data Definition Language
• Can define schema
• Can create/alter/delete tables and their attributes
Create Table
• Basic form
– CREATE TABLE