Corporation is a company which handles the auction of the different antiques.

The company wants to shift from the offline system to the online auction system. For

this they have consulted a database designer. Few of the data requirements

identified and conveyed to the designer are:

i. The users of the system are the buyers and sellers who are also known as the

members.

ii. Each member is uniquely identified by the member number. In addition, the name,

email id, password and other details of the member is also stored.

iii. The transaction to be made from seller to buyer is online for which the details of

address for antique to be shipped and account details are required.

iv. Each antique to be sold has a unique object number. Other details like description

of antique, initial bid value, start and the end date of the auction, etc. is mentioned.

v. A stipulated time period is fixed for each bidding.

vi. Once the auction time ends the buyer who has made the highest bid makes

payment to the seller.

vii. All the details of the bidder including the bid time and price is stored.

Based on the above requirements answer the following questions. Make additional

assumptions as necessary.

a) Identify the various entities, attributes and relationship from the above details.

b) Design an ER diagram for the above company.

c) Justify the designing of your ER diagram.

d) Mention the cardinality ratios also.

e) Map this ER Diagram to the relational model.

[6* 5 = 30 MARKS]

 

Consider the situation given in the case study for developing a Web Application

using Node.js and MySQL for managing all the tasks and activities of a student

within an Institution.

Page 2 of 4Page 3 of 4

Modules Required:

a) Login and Authentication System

b) Add a new student: The student details will have first name, last name, email,

contact no., department, and address. While adding a new student ensures that

email and contact no does not already exists in the records.

c) Update student records: The student can update the address, Email and contact

number but other details are not modifiable.

d) Delete student record: Whenever student pass out the organization then based on

his email or contact number his/her record should be removed from the system.

e) Search the students The student can be searched based on the given input string

that matches to any field of his/her record.

[8*5 = 40 MARKS]

 

QUESTION 3 [20 MARKS]

a) Explain in which Normal form the given table is.

b) Find the primary key (PK) for this relation & explain your choice

c) Find the fully functional dependencies on the PK & the partial dependencies on

the PK

AME ID NA

d) (iv)Find the transitive dependencies

e) Normalise the table and Express the table in DML language & show the PK and

Foreign Key (FK) in all the normalised relations

 

Represent the following query in Relational Algebra:

Select DOB AS 'BirthDate', empno AS 'EmpNumber' from Employee;

 

QUESTION 5

For the table below create an

a) index on an appropriate column

b) and a view of a virtual table containing the columns such as Customer_Name,

Region, Product_purchased and Amount

 
 

For fear of person-to-person transmission of the Coronavirus, some people have argued for the nationwide adoption of electronic retailing. Describe five mechanisms for electronic retailing. [10 Marks]