Converting ER diagrams to relational schema in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to represent an entity as a table with its primary key.

DBMS Theory

CREATE TABLE [1] (ID INT PRIMARY KEY);

Drag options to blanks, or click blank then click option'

AAttribute

BRelation

CEntity

DTuple

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Relation' instead of the entity name as table name.

Confusing attribute names with table names.

In relational schema, an entity from an ER diagram is converted into a table named after the entity.

2fill in blank

medium

Complete the code to add a foreign key for a one-to-many relationship.

DBMS Theory

ALTER TABLE Orders ADD COLUMN [1] INT, ADD FOREIGN KEY ([1]) REFERENCES Customers(ID);

Drag options to blanks, or click blank then click option'

ACustomerID

BOrderID

CProductID

DEmployeeID

Attempts:

3 left

💡 Hint

Common Mistakes

Using OrderID as foreign key in Orders table.

Using unrelated column names like ProductID.

In a one-to-many relationship, the foreign key is added to the 'many' side table referencing the 'one' side primary key.

3fill in blank

hard

Fix the error in the relational schema for a many-to-many relationship.

DBMS Theory

CREATE TABLE [1] (StudentID INT, CourseID INT, PRIMARY KEY (StudentID, CourseID));

Drag options to blanks, or click blank then click option'

AEnrollment

BStudent

CStudentCourse

DCourse

Attempts:

3 left

💡 Hint

Common Mistakes

Naming the table after only one entity.

Not including both foreign keys as primary key.

Many-to-many relationships are represented by a new table (often called Enrollment) with foreign keys from both related entities.

4fill in blank

hard

Fill both blanks to create a table for a weak entity with a composite primary key.

DBMS Theory

CREATE TABLE [1] (OwnerID INT, [2] INT, PRIMARY KEY (OwnerID, [2]));

Drag options to blanks, or click blank then click option'

ADependent

BDependentID

CDependentName

DOwnerName

Attempts:

3 left

💡 Hint

Common Mistakes

Using attribute names that are not keys.

Not including both keys in the primary key.

Weak entities require a composite key including the owner's key and their own partial key.

5fill in blank

hard

Fill all three blanks to define a table with an attribute and a foreign key for a multivalued attribute.

DBMS Theory

CREATE TABLE [1] (EntityID INT, [2] VARCHAR(100), PRIMARY KEY (EntityID, [2]), FOREIGN KEY (EntityID) REFERENCES [3](ID));

Drag options to blanks, or click blank then click option'

APhoneNumbers

BPhoneNumber

CEntity

DEntities

Attempts:

3 left

💡 Hint

Common Mistakes

Using the main entity table name for the multivalued attribute table.

Not including both keys in the primary key.

Multivalued attributes are represented by a separate table with a foreign key to the main entity and the attribute value.