A foreign key is a field in one table that refers to the primary key of another table.

It is used to establish a relationship between two tables and maintain referential integrity.

Example Consider the following tables:

Table : Bank_Account

Table : Branch

Here, ACode in the Bank_Account table is a foreign key which refers to the primary key ACode of the Branch table. Using this foreign key, the city of Amrita can be obtained from the Branch table.

An Alternate Key in a Relational Database Management System (RDBMS) is a candidate key that is not chosen as the primary key. It is still unique and can be used to identify records, but it is secondary to the primary key.
Example In a table of employees, the Employee ID could be the primary key, while the Email address (which is also unique) would be an alternate key. Both can uniquely identify a record, but only primary key.

A table with one primary key and two alternate keys will have three candidate keys.
For example:

Here, StudentlD, Email, and PhoneNumber are all candidate keys (with StudentlD being the primary key).

Some limitations of DBMS are given below:
(i) High Cost DBMS requires various software, hardware and highly intelligent people for operating and maintaining the database system. It increases its cost.

(ii) Database Failure If database is corrupted due to power failure or any other reason, our valuable data may be lost or whole system stops.
(iii) Data Quality With increased number of users accessing data directly. There are enormous opportunities for users to damage data. So, it is not easy to provide a strategy to support multiple users to update data simultaneously.

The need of the database system arose in the early 1960s in response to traditional file processing system. To overcome the disadvantages of file processing system, the database system was designed. Some of the major disadvantage of file processing system are given below:

(i) Data redundancy

(ii) Data inconsistency

(iii) Lack of security features

(iv) Handling new queries is difficult.

(i) Candidate Keys CID
(ii) Primary key CID

A primary key is a set of one or more attributes that can uniquely identify tuples within the relation.

Example

TABLE: ITEM

The attribute ItemNo is a primary key in ITEM table as it contains unique value for each tuple in a relation.

(i) Domain It is a set of possible values for an attribute. A domain is said to be atomic if elements of the domain are considered as indivisible units.

(ii) DB2 It is a Relational Database Management System (RDBMS), fully featured, high performance database capable of handling large quantities of data and concurrently serving many users.

The columns of a table are referred to as attributes. An attribute is also known as field, which is reserved for a specific piece of data. The rows of a table are referred to as tuples.

Degree = 10(No. of columns)

Cardinality = 10 - 3 = 7(No. of rows)

Degree The number of attributes or columns in a table is called the degree of the table.

The degree of the given table is 3

Cardinality The number of rows or records in a table is called the cardinality of the table. The cardinality of the given table is 2.

(a) (i) Degree is 5. Cardinality is 6.
(ii) MOVIEID should be made the primary key as it uniquely identifies each record of the table.
Or

(b) (i) MOVIEID is the candidate keys in table MOVIEDETAILS.

(ii) MOVIEID is the foreign key if the tables MOVIEDETAILS and SCHEDULE are related in a database.

Advantages are:

(i) No redundant data

(ii) Data security
(iii) Easy access of data
(iv) Data consistency and integrity

DBMS has the following advantages:
(i) Reduced data redundancy

(ii) Elimination of inconsistency

(iii) Data sharing

(iv) Data integrity

(v) Data security

(vi) Ease of application development

Some commonly used DBMS software packages are:

(i) MySQL

(ii) Oracle

(iii) Postgre SQL

(iv) DB2

(v) MS-SQL

(vi) Sybase

(a) STUDENT: RollNo

PROJECT: ProjectNo

PROJECT ASSIGNED: Registration_ID

(b) ProjectNo

(c) Yes, Registration_ID in STUDENT table could be an alternate key if its unique (which appears true from schema).

(d) No, RollNo being the primary key must be unique for each row.

(a) Yes, assigning Movie_ID as the primary key in the MOVIE relation is correct, as it uniquely identifies each movie.

(b) No, assigning AudiNo alone as the primary key in the AUDI relation is not correct, as an auditorium can screen multiple movies. A composite key of AudiNo and Movie ID would be more appropriate to uniquely identify each record.
(c) Yes, Movie_ID in the AUDI relation is a foreign key referencing the Movie_ID in the MOVIE relation.

(a) (i) Yes it can be inserted because UColor attribute allows NULL values and there is no NOT NULL Constraint on it.
(ii) No, it cannot be inserted because UCode=4 already exists in the UNIFORM table and primary key values must be unique.
(iii) No, it can not be inserted because UName attribute is defined as NOT NULL and NULL values is not allowed for it.
(b) (i) No, this tuple cannot be inserted because the cost = 0 violates the CHECK constraint (cost > 0)

(ii) No, this tuple cannot be inserted because UCode = 9 does not exist in the UNIFORM relation and hence foreign key constraint is violated.

(a) Candidate keys for EMPLOYEE: AadharNumber, EmployeelD

(b) Tables: EMPLOYEE, DEPENDENT; Key: EmployeelD

(c) Degree of EMPLOYEE: 5, Degree of DEPENDENT: 3

(a) No, a NULL value cannot be assigned because every student must give exactly one sports preference, and NULL violates this rule.
(b) Roll no17 violates the entity integrity constraint, as the same Roll_no appears more than once. This can be prevented by defining Roll_no as PRIMARY KEY, which ensures that each student gives only one preference.

(c) No, this tuple cannot exist because Roll_no is NULL, and a primary key attribute cannot have NULL values. Every preference must be associated with a valid student roll number.

Limitations of File Systems Overcome by Relational DBMS:

(1) Data Redundancy Relational DBMS minimizes duplication through normalization.

(2) Data Integrity DBMS enforces constraints to maintain accurate data, unlike file systems.

(3) Data Access DBMS provides complex querying capabilities using SQL, while file systems require manual data handling.

(4) Concurrency Control DBMS supports multiple users accessing data simultaneously, ensuring data consistency.

(5) Data Security DBMS offers advanced security and user permissions features, including encryption, which file systems lack.

A Database Management System (DBMS) avoids redundancy through normalization, which organizes data into tables to eliminate duplicate data and dependencies.

It enforces relationships and constraints, ensuring that data is stored once and referenced rather than duplicated, thereby maintaining data integrity and consistency.

(a) Table

(b) Data Dictionary

(c) Primary key

(d) NULL

(e) Alternate key

(f) DBMS

Differences between primary key and candidate key:

Database The database is a shared collection of logically related data, designed to meet the information needs of an organization.

A database is a computer based record keeping system whose over all purpose is to record and maintains information.

The database holds not only the organization's operational data but also a description of this data. For this reason, a database is also defined as a self describing collection of integrated records.
The description of the data is known as the data dictionary or metadata (data about data). DBMS A Database Management System (DBMS) is a collection of programs that enables users to store, modify and extract information from a database as per the requirements. It acts as an interface between the application program and the operating system to access or manipulate the database.

A database management system is a piece of software that provides services for accessing a database, while maintaining all the required features of the data.

Commercially available database management system in the market are dbase, FoxPro, Oracle, MySQL and DB2 etc.

(a) No. the Roll No attribute in the STUDENT table is marked as primary key and NOT NULL. Therefore, inserting a student record with a missing Roll No value would violate the NOT NULL constraint and is not allowed.
(b) No, because Registration_ID is a candidate key (alternate key) with a NOT NULL constraint in the schema, so it cannot be NULL.
(c) Yes, the SubmissionDate attribute in the PROJECT table does not have a NOT NULL constraint specified in the schema. Therefore, it is possible to insert a project detail without a SubmissionDate value.

(d) No, we cannot perform this operation. ProjectNo in PROJECT ASSIGNED table serves as a foreign key that references the primary key in the PROJECT table. Since ProjectNo "206" is not present in the PROJECT table, it cannot be inserted into the PROJECT ASSIGNED table.

(a) Relation for items and prices:
• Relation CanteenItems

• Attributes ItemID (INT, Primary Key), ItemName (VARCHAR), Price (DECIMAL)

• Restriction Use a Unique constraint on ItemName to ensure each item is stored only once.

(b) Relation for purchases and billing:
• A new relation is required: PurchaseRecords
• Attributes: BillID (INT, Primary Key), ItemlD (INT, Foreign Key referencing CanteenItems), Quantity (INT) Restrictions:

(i) Primary Key BillID (to ensure unique bills).
(ii) Foreign Key ItemlD (to ensure items are available in the canteen).

(c) Storing calories

• The attribute Calories should be stored in the CanteenItems relation since it is related to each item.

• Attribute: Calories (INT).

(i) (A) 4,4

(ii) (C) Stud_id

(iii) (D) 2,2

(iv) (C) Stud_id

(v) (C) T_id