Scribd
Upload
15 views3 pages

Chapter 2 DS Assignment

The document defines key database concepts: superkey, candidate key, primary key, and foreign key, explaining their roles in uniquely identifying records and maintaining relationships between tables. It discusses the implications of having multiple advisors for a student, concluding that the primary key for the advisor relation should be a combination of student ID and advisor ID. Additionally, it provides a schema diagram for employee, works, and company tables, highlighting primary and foreign keys and the many-to-many relationship between employees and companies.

Uploaded by

Akash Warke
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
15 views3 pages

Chapter 2 DS Assignment

The document defines key database concepts: superkey, candidate key, primary key, and foreign key, explaining their roles in uniquely identifying records and maintaining relationships between tables. It discusses the implications of having multiple advisors for a student, concluding that the primary key for the advisor relation should be a combination of student ID and advisor ID. Additionally, it provides a schema diagram for employee, works, and company tables, highlighting primary and foreign keys and the many-to-many relationship between employees and companies.

Uploaded by

Akash Warke
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Question 1

Define the terms: superkey, candidate key, primary key, and foreign key.

Superkey

A collection of one or more characteristics that can be used to uniquely identify a record in a
table is called a superkey. It might have extra characteristics that aren't required for
uniqueness.

Candidate Key

A minimal superkey is called a candidate key. Every record is uniquely identified by it, and
its uniqueness cannot be diminished by removing any attribute. There may be more than
one candidate key in a table.

Primary Key

A primary key is the candidate key chosen to uniquely identify records in a table. It does not
allow duplicate or NULL values and is used as the main identifier.

Foreign Key

A foreign key is an attribute in one table that refers to the primary key of another table. It
helps maintain a relationship between tables and ensures data consistency.

--------------------------------------------------

Question 2

Consider the advisor relation:


advisor(s_id, i_id)

with s_id as the primary key. Suppose a student can have more than one advisor. Then,
would s_id still be a primary key of the advisor relation? If not, what should the primary key
be? Explain.

Answer:

No, s_id would not remain a primary key if a student can have more than one advisor.

Explanation:

A primary key must uniquely identify each record. If a student has multiple advisors, the
same s_id will appear in multiple rows with different i_id values. This breaks the uniqueness
rule of a primary key.

Correct Primary Key:

The combination of (s_id, i_id) should be the primary key because together they uniquely
identify each advisor assignment.

--------------------------------------------------

Question 3

Draw a schema diagram for the following database, where primary key attributes are
underlined:

employee(person_name, street, city)

works(person_name, company_name, salary)

company(company_name, city)
Schema Description:

employee

Primary Key: person_name

Attributes: street, city

company

Primary Key: company_name

Attributes: city

works

Primary Key: (person_name, company_name)

Attributes: salary

Foreign Key: person_name references employee(person_name)

Foreign Key: company_name references company(company_name)

Explanation:

The relationship between companies and employees is depicted in the works table. The
relationship is many-to-many since a company may have multiple employees and an
employee may work for multiple companies. The salary attribute is kept in the works table
since it is dependent on both the employee and the business.

You might also like