www.cardiff.ac.uk/medic/irg-clinicalepidemiology
Logical database design
Copyright By PowCoder代写 加微信 powcoder
Information modelling
& database systems
in the last lecture we learnt about issues surrounding conceptual database design
in this lecture we will consider logical database design
this is where we turn a conceptual model into a set of tables for implementation
Mapping to tables
a conceptual model can be mapped to a set of tables mechanically
each entity maps to a table
all attributes of the entity become columns
of the table
each many-to-many relationship maps to a table
primary keys of the two tables (entities) together with attributes of the relationship (if any) become columns of the table
for one-to-many relationships, the primary key from the “one” side becomes a foreign key
ER models & SQL
ER models can be implemented in SQL
entities, attributes and relationships can be expressed in SQL
many-to-many relationships are a problem, so they should be removed
ER diagram SQL
entity table
instance row
attribute column
M:1 relationship foreign key : primary key
Implementing ER models in SQL
entities become tables
attributes of an entity become columns of the table
M:1 relationships are represented by foreign keys
the primary key from the table on the 1–side becomes a foreign key in the table on the M–side
Entities and attributes
each entity becomes a table
the name of the table is often the name of the entity
the attributes become columns of the table
CREATE TABLE Student
ID INT NOT NULL,
name VARCHAR(50) NOT NULL,
address VARCHAR(50),
year INT DEFAULT 1,
PRIMARY KEY (ID)
Relationships
depends on the type
1:1 are usually not used, or can be treated as a special case of M:1
M:1 are represented as a foreign key from the M–side
to the 1–side
M:M are split into two M:1 relationships
Relationships
Enrolment table
will have columns for the Exam and Assignment attributes
will have a foreign key to the Student table (ID) for the “has” relationship
will have a foreign key to the Module table for the “in” relationship
Relationships
CREATE TABLE Enrolment
ID INT NOT NULL,
code CHAR(6) NOT NULL,
assignment INT,
exam INT,
PRIMARY KEY (ID, code),
FOREIGN KEY (ID)
REFERENCES Student (ID),
FOREIGN KEY (code)
REFERENCES Module (code)
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com