www.cardiff.ac.uk/medic/irg-clinicalepidemiology
Conceptual database design
Copyright By PowCoder代写 加微信 powcoder
Information modelling
& database systems
in the previous lectures, we focused on the relational data model and studied how data is logically stored in a relational database and how SQL may be used to retrieve the data from it
from this lecture we will turn our attention to design issues for databases
Conceptual database design
Database development tasks
conceptual design
implementation
maintenance
Database system development
requirements
application
requirements
conceptual
fully defined
performance
expectations
Database design
before implementing a database, we need to design it
questions to consider
What tables, keys and constraints are needed?
What is the database going to be used for?
conceptual design build a model independent of the
choice of a DBMS
logical design create the database in a chosen DBMS
physical design physically store the database on disk
Conceptual database design
brainstorming
observation
interviews
requirements
specification
Expressing requirements
to express the gathered requirements, we use a modelling language, e.g.
ER Entity–Relationship
UML Unified Modelling Language
they provide a standard way to visualise database design
UML example:
association
(relationship)
When specification is abstract …
some specifications do not provide enough detail about the software to be developed
How to extract a conceptual database design from such abstract specification?
processing
Starting with use cases
use case is a list of actions or event steps that defines the interactions between a role (actor) and a system to achieve a goal
think about main things that you would talk about when discussing the use case with the bank fellow developers
A customer withdraws cash from a cash machine and the account on bank’s central database is updated.
Deriving CRC cards
class–responsibility–collaboration (CRC) cards are a brainstorming tool used in the design of OO software
writing out a scenario which identifies major actors and their actions:
nouns should turn into the classes
verbs should turn into the responsibilities
collaborators are the other cards with which
the card interacts
Keep balance
Transaction
responsibility
collaboration
When specification is detailed …
A company has a number of employees. We describe employees by their ID, name, address and birth dates. The company also has several projects. For each project, we are interested in its identifier, title and the start date. Each employee may be assigned to some project(s).
How to extract a conceptual database design from such detailed specification?
Identify nouns and verbs
candidate classes: Company, Employee, Project
candidate attributes:
Employee: ID, name, address, birth date
Project: ID, title, start date
candidate relationships:
(company) Has (employees)
(employees) Assigned to (projects)
A conceptual model
a possible conceptual model for this specification
Q: Why do we not create the Company class?
is assigned to
When specification is by example …
Apply abstraction
determine if some data can be described generically
e.g. milk, beans, bread are all products
this generic description can be a class
determine from which sources the data is derived
e.g. total is the result of a transaction
add relationships that make sense
e.g. (transaction) contains (products)
A conceptual model
a possible conceptual model for the receipt
Q: Where do you record Quantity and Amount?
Transaction
receipt no
description
No fast rules in conceptual design
creating a database design from specification is much harder than implementing one
requires a lot of practice and experience
we do not always have definitive answers for questions such as
Is Company really a class or just an attribute of employee (describing where the employee works)?
No fast rules in conceptual design
this may surprise you, but remember that:
the purpose of conceptual design is to iteratively clarify vague points with clients before implementation
there is no single correct design for any situation and only the experience can help
specification may be vague, but your design will not
so, practice, practice and practice!
Entity–relationship (ER) model
Entity–relationship (ER) model
ER modelling is used for conceptual design
ER model represents a data model using basic graphical symbols to show the organisation of & relationships among data
a formal description of each entity in terms of its attributes
description of the meaning of relationships
description of any constraints on the system and any assumptions made
Entity-relationship (ER) model
basic elements
entities – objects or items of interest
attributes – properties of an entity
relationships – links between entities
e.g. a University database
possible entities: Students, Modules and Lecturers
Students might have attributes such as ID, Name and Course
Students could have relationships with Modules (enrolment) and Lecturers (tutor/tutee)
ER diagrams
ER models are often represented as ER diagrams
give a conceptual view of the database
independent of the choice of DBMS
can identify some problems in a design
ER notations
unfortunately, there is no
standard representation of
ER diagrams
many different conventions
are available
in this module we will
concentrate on the
Chen notation
entities represent objects or things of interest
physical things like students, lecturers or lecture rooms
more abstract things like modules, courses or exams
entities have
general type, e.g. Lecturer or Module
instances of that particular type, e.g. and ć are instances of Lecturer
attributes, e.g. name, occupation or email address
Diagramming entities
an entity is usually drawn as a box with rounded corners
the box is labelled with the name of the type of objects represented by that entity
Attributes
attributes are facts, aspects, properties or details about an entity, e.g.
Students have IDs, names, courses, addresses, …
Modules have codes, titles, credits, levels, …
attributes have
associated entity
domains of possible values
values for each instance of the entity
Diagramming attributes
attributes may be drawn as ovals
each attribute is linked to its entity by a line
the name of the attribute is written in the oval
Relationships
relationships are associations between two or more entities, e.g.
each Student takes several Modules
each Module is taught by a Lecturer
each Lecturer works in a School
relationships have
a set of entities that participate in them
degree – the number of entities that participate (most have degree 2 – binary relationships)
cardinality ratio
Cardinality ratio
each entity can participate in zero, one or more instances of a given relationship
this leads to three types of relationships
one to one 1:1
each lecturer has one office, and
each office accommodates only one lecturer
one to many 1:M
a lecturer may tutor many students, but …
each student has only one tutor
many to many M:M
each student takes several modules, and …
each module is taken by several students
Diagramming relationships
relationships are represented by lines linking the participating entities
the name of a relationship is given in a diamond box
the end of the link shows cardinality
Removing M:M relationships
many to many relationships are difficult to represent
we can split such relationship into two 1:M relationships by
… using an entity to represent the original M:M relationship
Creating ER models
to create an ER model, we use a problem description to identify
attributes
general guidelines
entities are things or objects, so they are often described by nouns
attributes are facts or properties, also described
verbs often describe relationships between entities
relationships
cardinality ratios
A university consists of a number of departments. Each department offers several courses. A number of modules make up each course. Students enrol in a particular course and take modules towards the completion of that course. Each module is taught by a lecturer from the appropriate department, and each lecturer tutors a group of students.
Example – entities
A university consists of a number of departments. Each department offers several courses. A number of modules make up each course. Students enrol in a particular course and take modules towards the completion of that course. Each module is taught by a lecturer from the appropriate department, and each lecturer tutors a group of students.
Example – relationships
A university consists of a number of departments. Each department offers several courses. A number of modules make up each course. Students enrol in a particular course and take modules towards the completion of that course. Each module is taught by a lecturer from the appropriate department, and each lecturer tutors a group of students.
Example – ER diagram
entities: Department, Course, Module, Lecturer, Student
Example – ER diagram
each department offers several courses
Example – ER diagram
a number of modules make up each course
Example – ER diagram
students enrol in a particular course
Example – ER diagram
students take modules
Example – ER diagram
each module is taught by a lecturer
Example – ER diagram
a lecturer from the appropriate department
Example – ER diagram
each lecturer tutors a group of students
Example – ER diagram
Entities vs. attributes
sometimes it is hard to tell if something should be an entity or an attribute
they both represent objects or facts about the world
they are both often described by nouns
general guidelines:
entities can have attributes, but attributes have no smaller parts
entities can have relationships between them, but an attribute belongs to a single entity
Example – entities vs. attributes
entities or attributes:
description
products, suppliers and addresses all have smaller parts so we can make them entities
others have no smaller parts and belong to a single entity
street address
phone number
Example – entities vs. attributes
Example – relationships
each product has a supplier
each product has a single supplier but there is nothing to stop a supplier supplying many products
many to one relationship
each supplier has an address
a supplier has a single address
it does not seem sensible for two different suppliers to have the same address
one to one relationship
Example – relationships
One to one relationships
some relationships between entities, A and B, might be redundant if …
it is a 1:1 relationship between A and B
every A is related to a B and every B is related to an A
e.g. the supplier–address relationship
is one to one
every supplier has an address
we do not need addresses that are not related to a supplier
Redundant relationships
we can merge the two entities that take part in a redundant relationship
they become a single entity
the new entity has all the attributes of the original ones
Example – ER diagram
Creating ER diagrams
from a description of the requirements identify
attributes
draw an ER diagram and then …
look at one to one relationships as they might be redundant
look at many to many relationships as they might need to be split into two one to many relationships
relationships
cardinality ratios
Debugging designs
with a bit of practice ER diagrams can be used to plan queries
we can look at the diagram and think about how to find specific information
if you cannot find it, then you may need to change the design
Debugging designs
find the instance of the Module entity
with title “Database Systems”
find instances of the Enrolment entity
with the same Code as the result of (1)
for each instance of Enrolment in the
result of (2) find the corresponding
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com