fundamentals of database systems
Every year IT show is conducted in Singapore. The organizer of the IT show decided to develop a database to keep the detail of each IT show conducted. Several companies participate in the IT-show.
A company may be a manufacturer, a dealer of products or Sales Company. A company distributes its own catalogue of products. The companies sell a number of products, such as computers (desktops, laptops, servers etc), I/O devices (printers, monitors, keyboards etc) and computer memory devices (hard disk, CDS, USB etc). TV and related products are also displayed and sold. Cameras and associated products are also sold in the IT show.
A product may be sold by many companies and a company may sell many products. First a company must register for participation. The Organizer of the IT show keeps the records of all the companies who have participated in the previous IT shows also.
Whenever a customer makes a purchase a unique number is given to the customer. The details of customers who made purchase(s) in the IT show are recorded. The purchase details include the product detail and invoice detail. A customer may purchase many products and a product may be purchased by many customers.
Though it is not compulsory, all the visitors are requested to scan their IC card, Employment card, or Work Permit card. The foreign visitors may also enter their passport number and address on the systems kept at the entrances of the IT show.
QUESTION 1
In the design of a database using top down method, the first step is drawing the conceptual model (EER diagram).
With reference to the case study given above, perform the following tasks in drawing the EERD:
(a) Identify all the real world entities giving a candidate key and suitable attributes for each entity.
(Note: A maximum of 6 attributes should be given for each entity).
[12 marks]
(b) Identify an entity with a multi-valued attribute.
[1 mark]
(c) Identify a weak entity present in the case study and determine its relationship with the owner entity.
[1 mark]
(d) Identify relationships with a cardinality ratio 1:1.
[1 mark]
(e) Identify relationships with cardinality ratio 1: M.
[1 mark]
(f) Identify relationships with cardinality ratio N: M.
[1 mark]
(g) Identify the Super/ subtype entity available in the given scenario. Also identify the constraints on the generalization/specialization as either disjoint or overlapping.
[8 marks]
(h) Identify any one possible ternary relationship available in the given scenario.
[1 mark]
(I) Give an example from the above scenario identify the additional semantic feature of a relationship with
(I) total participation,
(ii) partial participation.
