Q1Explain the difference among the terms Primary Key Candida

Q1:Explain the difference among the terms Primary Key, Candidate Key and Super Key with suitable examples.

Q2: Consider the following schema: Supplier(sid: integer, sname: string, address: string) Part(pid: integer, pname: string, colour: string) Catalog(sid: integer, pid: integer, cost: real) The relation Supplier stores suppliers and the key of that relation is sid. The relation Part stores parts, and pid is the key of that relation. Finally, Catalog stores which supplier supplies which part at which cost. The key is the combination of the two attributes sid and pid. A. Write the queries in relational algebra for the following: 1. Find the names of suppliers who supply some red part. (0.25) 2. Find the IDs of suppliers who supply some red or green part. (0.25) 3. Find the IDs of suppliers who supply some red part or are based at 21 George Street. (0.50) 4. Find the IDs of suppliers who supply some red part and some green part. (0.50) B. For each of the following relational algebra queries, write in English what they mean. 1. sname(colour=’red’(Part) cost<100 (Catalog) Supplier) (0.25) 2. sname(sid(colour=’red’(Part) cost<100 (Catalog)) Supplier) (0.25)

note please answer all of them at one time and by computer

Solution

Answer:
1.

1. Primary Key: A primary key is designated to uniquely identify all table records. A primary key\'s main features are-
- It must contain a unique value for each row of data.
- It cannot contain null values
There can be only one and only one primary key for the data.

2. Candidate Key: A candidate key is that can uniquely identify any database record without referring to any other data. Each table may have one or more candidate
keys, but one candidate key is unique, and it is called the primary key.

3. Super Key: A superkey is that uniquely identifies any row within a relational database management system (RDBMS) table.
A candidate key is a closely related concept where the superkey is reduced to the minimum number of columns required to uniquely identify each row.

Example of all the three keys are-

Employee(EId, EName, EAddress, ENumber, EAge)
Considering the above schema of employee-

Primary key- EId
Candidate Key- EId+ENAme or EId+EAddress
Super Key- EId+EName+EAge

2. A.

1. The name of suppliers who supply some red part are-
sname(colour=’red’(Part))
2. The id of suppliers who supply some red or green part are-
sid(colour=’red’(Part) /\\ colour=’green’(Part))
3. The ids of suppliers who supply some red part or are based at 21 George Street
   sid(colour=’red’(Part) /\\ address=’21 George Street’(Supplier))
4. The ids of suppliers who supply some red part and some green part
sid(colour=’red’(Part) \\/ colour=’green’(Part))

B. 1. It will display all the name of suppliers who supply some red parts and whose cost of supplying is less than 100 given that the sid of both
that is in supplier and catalog should be equal.

2. It will first display all the id of suppliers who supply red parts and cost is less than 100 from catalog table and then matches the id from
the supplier one and then project the name of suppliers.