QUESTION 2 Which of the following are correct statements about Primary Key of a
ID: 3797986 • Letter: Q
Question
QUESTION 2
Which of the following are correct statements about Primary Key of a table?
Primary Key uniquely identifies a row in the table
The value of a column which is constituent of the primary key cannot be null
Primary key columns can accept only numeric values
It is possible that the primary key can be combination of multiple columns
QUESTION 3
Provide an example where foreign key column value of a table can be null.
QUESTION 4
Study the data in the table below. The first row of the table contains the column names.
Table: LOANS
In the above table, the SIMPLE_INTEREST column is computed using the values in the columns PRINCIPAL_AMOUNT, TERM_YEARS and YEARLY_RATE.
Is the table Normalized?
Yes
No
QUESTION 5
The dataset below shows list of employees of an organization and the departments that the employees are working in. The EMPLOYEE_ID column of the dataset uniquely identifies an employee of the organization. The DEAPARTMENT_ID column of the dataset uniquely identifies a department of the organization. An employee of this organization can work only in one department of the organization.
Based on the information provided, please normalize the given dataset. Your response should clearly indicate resultant tables after the normalization along with their colums. Also please indicate all the primary keys and applicable foreign keys.
Note: The first row of the above table indicates the names of the columns of the dataset.
QUESTION 6
From the list below, identify ALL the options to implement data integrity in the Database design.
Foreign Keys
NOT NULL constraints
Identification of actors for Use Cases
CHECK Constraints
Using the correct data types for the columns
QUESTION 7
Please study the data in the table below, and based on the data in the table, identify the column that can NOT be the primary key. Please note that the first row of the table contains names for the columns of the table.
TEST_TABLE
REASON_ID
SUCCESS_NUMBER
REFERENCE_ID
ATTEMPT_ID
QUESTION 8
Explain how Referential integrity can be enforced between two tables. You may use examples if it makes easy for you to explain.
QUESTION 9
Study the data in the tables named EMPLOYEE and EVALUATION below. The first row of each of these tables contains the names for the columns of the table.
EMPLOYEE
EVALUATION
Based on the data in these tables, the column EMPLOYEE_ID from EVALUATION table can be a foreign key referencing EMPLOYEE_ID in EMPLOYEE table.
True
False
A.Primary Key uniquely identifies a row in the table
B.The value of a column which is constituent of the primary key cannot be null
C.Primary key columns can accept only numeric values
D.It is possible that the primary key can be combination of multiple columns
Explanation / Answer
2) A) and B and D
Primary key:
Primary Key uniquely identifies a row in the table
The value of a column which is constituent of the primary key cannot be null (Primary key can not have null values)
It is possible that the primary key can be combination of multiple columns
-----------------------------------------------------------------------------------------------------------------------------
4)No the table is not Normalised
LOANS(LOAN_ID,PRINCIPAL_AMOUNT,TERM_YEARS,SIMPLE_INTEREST)
Rate(PRINCIPAL_AMOUNT,YEARLY_RATE) // new table with two columns.
------------------------------------------------------------------------------------------------------------
5)
Employee(EMPLOYEE_ID,EMPLOYEE_FIRST_NAME,EMPLOYEE_LAST_NAME,DEPARTMENT_ID) // primary keyEMPLOYEE_ID foreign key:DEPARTMENT_ID
Department (DEPARTMENT_ID,DEPARTMENT_NAME) // primary key DEPARTMENT_ID
Employee:
Department:
-------------------------------------------------------------------------------------------
6)data integrity in the Database design.
Foreign Keys
NOT NULL constraints
CHECK Constraints
------------------------------------------------------
7) option B) SUCCESS_NUMBER
with SUCCESS_NUMBER we are getting two rows when SUCCESS_NUMBER=8, so it is not uniquely identifying, so it cant be a primary key.
--------------------------------------------------------------------------
8)Referential integrity:
It is defined on a column or set of columns in one table , such that the values defined in these columns should match with the values defined in a column of other table(refernced table).
cases:
when referenced data is updated or deleted , all associated dependent data is set to null
in exercise 5) if we delete/update the record where department id is 2 in the department table
then
in the employee table the respective department id is set to Null which was previously 2
Disallows the update or deletion of referenced data
if we want to add a new employee in employee table then he is not working in other department so set department id as null
---------------------------------------------------------------------------------------------
9)False
In Evalution table EMP_ID is 8 which is not present in parent i.e Employee table
EMPLOYEE_ID EMPLOYEE_FIRST_NAME EMPLOYEE_LAST_NAME DEPARTMENT_ID 100 SCOTTY JORDAN 1 110 MICHAEL PIPPEN 2 113 KOBE NASH 3 115 STEVE BRYANT 2