LAB 2

Questions Asked by Students

Do we have to drop the tables?

• Yes, it’s essential to drop the tables. Ensure you’ve tested that each table has been successfully removed, as they may need to be dropped in a specific order due to foreign key constraints.

Designing an ER diagram

Entity: a thing (physical or logical) that exists and is distinguishable from others (by distinctive features, properties = attributes) A group consisting of all “similar” entities forms an entity set. Relationship Sets: Diamonds represent the relationship sets defined among two or more entity sets. Each entity set has a set of attribute types associated with it, and must have a distinguishable key. Rectangles represent Entity Sets, and Circles represent attributes. The underlined attribute represents the key of that entity set. Cardinalities of the relationship set: One to One, One to Many, Many to Many

Mapping from ER model to Relational Model

Since the ER model is a “half-model”, we will eventually need to convert it to a Relational Model.

An entity set would correspond to a relation (table). Each attribute in the entity set would correspond to an attribute (column). Each entity would correspond to a record (row). The keys of the entity sets are the unique identifiers, so they correspond to Primary Keys of the tables.

Many-To-Many relationship sets become a relation. $A(a,c)$ and $B(b,d)$, then $C(a (FK),b (FK))$
Many-To-One relationship sets: the primary key of the “restricted” entity set becomes a foreign key for the “non-restricted” one. One-To-One relationship sets: the primary key of the “restricted” entity set becomes a foreign key AND unique for the “non-restricted” one.

SQL DDL, DML, etc.

CREATE TABLE <table_name> (...);

DROP TABLE <table_name>;

INSERT INTO <table_name> [(<column_name1> [<column_name2>,
...])]
VALUES (<value1> [, <value2> , ...]);

DELETE FROM <table_name>
[WHERE <condition>]

UPDATE <table_name>
SET <column_name1> = <value1> [,<column_name2> = <value2>,
...]
[WHERE <condition>];

Other Comments

Understand constraints! Explain every step of your thought process. DO NOT just attach an ER diagram without explanation!