Corporation is a company which handles the auction of the different antiques. The company wants to shift
Question:
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
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
Financial Accounting and Reporting
ISBN: 978-0273744443
14th Edition
Authors: Barry Elliott, Jamie Elliott