Question

Q3. Convert the conceptual database into a relational database schema. Identify the primary key and foreign key constraints.

Q4. Show all the functional dependencies that should hold among the attributes.

Q5. Design relation schemas for the database that are each in 3NF. Specify the primary and foreign key attributes of each relation.

Hi expert!

Can you help me with the above questions? I'm not asking you to solve my questions but want to find what each questions actually requires me to show or answer.

Answer 1

Question 3:
For Convert the conceptual database into a relational database schema we need a conceptual model for the database in which various things are clearly defined:

• 1. Entities: In the conceptual model each entity is defined by using a rectangle box.
• 2. Attributes: Attributes are defined within the Entities. Attributes are the column name of the Table. Attributes are prime attributes, non-prime attributes, or foreign key attributes. An attribute that uniquely identifies the Table is called Primary key
• 3. Cardinalities: The connection between entities must be 1:1, 1:M, M:1, M:M.
• 4. Relationship name: The name by which two entities are connected

All these things need to convert the database into a relational database. There are the following steps need to convert them:

• 1. Convert the entity into a Table or Create a table for the entity
• 2. The attributes of the entity act as filed of a table with its data type.
• 3. Mention a Primary key for each table.
• 4. Now check the relationship exists between entities and base on of cardinalities the Fk is defined in a table:
• a) For 1:1 cardinalities the PK of one entity act as FK in another entity. It depends on you in which entity you set the FK.
• b) For M:1 cardinalities the PK of one order entity act as FK in M order entity.
• c) For M:M cardinalities the Pk of both the entities act as an FK in relationship Table of Both entities

Question 4:
Functional Dependencies between the attributes :
When one attribute depends on other attributes then the functional dependencies exist. The dependency of the non-prime attribute to the prime attribute is called Functional Dependencies.
In which one attribute is called determinant and the other attribute is called a dependent attribute.

Like a--> b   
a is dependent on b and a is act as Determinant and b is act as Dependent attribute.

Example :
StudentID -----> StudentName, StudentAddress
StudentName and StudentAddress is depend on StudentID

Question 5:

A database in 3NF when the data is in 2NF and there are transitively dependency exists. The transitive dependency exists between Non-prime attributes and prime attribute.
The Primary key is mention as per the requirement of the table and The Foreign key is mention according to the cardinalities given and the relational schema is build for the database.

Example:
EmployeeID ---> EmpName , EmpSalary , EmpPhoneNo.