CS代写 www.cardiff.ac.uk/medic/irg-clinicalepidemiology – cscodehelp代写


Conceptual database design

Copyright By cscodehelp代写 加微信 cscodehelp

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

Database system development
fully defined

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


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:


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?


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

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?


receipt no


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 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 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
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
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:
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
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
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


程序代写 CS代考 加微信: cscodehelp QQ: 2235208643 Email: kyit630461@163.com

Leave a Reply

Your email address will not be published. Required fields are marked *