The application scenario

The online sale company Best Deal needs to implement a database to manage its business data. The company has delegated this task to an IT service company. As an officer of the IT service company, you are delegated to be the designer and developer for the database. To accomplish this task, you will divide the design and development tasks into two assignments. In this assignment (assignment- 1), you will do the data modelling. In assignment-2, you will design and implement the database and relevant SQL queries. Consequently, a successful data modelling of this assignment is also important for assignment-2.

The Best Deal sells smart phones or services in two ways.

1. The brand new phones are only for sale. The sale is in auction style. A new phone can be listed with a starting price and available numbers etc for a few days (e.g. 3 days). During this period, a buyer can put a bid with an offered price for a listing. At the end of the period, the listing can have a number of bids. Based on the bids of a listing, sale transactions may happen between the sellers and buyers.

2. The used phones are only for rent. A buyer is to submit an application for renting a phone. As a result, rent transactions may happen between sellers and buyers.

3. As described above the customers of Best Deal are either sellers (selling or leasing phones) or buyers (buying new phones or renting the used phones). A seller must have a registered payment method (e.g. PayPal) for the income from selling and a registered payment method (e.g. PayPal) for paying management fees to the Best Deal. A buyer could have a registered payment method (e.g. PayPal) for paying any purchased items or just paying by credit card directly when buying. However either a seller or a buyer must have at least a phone or a mobile or an email contact.

The above application scenario was not given in very detail for the entities or relations because one of your tasks for this assignment is to analyse and make some assumptions on the business data of the Best Deal. You may need to do some research about the online sale data management, so browsing sale items on eBay is useful to understand and model the business data of such an online sale database.

The assignment specification

You are to develop an ER model for the data management of the Best Deal. You will provide the ER model in terms of:

1. The assumptions and business rules that you have made for the entities, relationships and cardinalities that involve in the Best Deal data management. Justify your assumptions if necessary. You need to make sure that the assumptions and the identified entities and the relationships cover the given application scenario.

2. An ER diagram. You need to use the standard notations (as given in the textbook) to present the ER diagram, clearly representing entities and relationships, cardinalities and primary keys and using super types and sub types if necessary. Note: use of a formal modelling tool such as Microsoft Visio is not required for the ER modelling.

3. Entity and relationship and cardinality definition. In textual description, you need to briefly define entities in terms of attributes and relationships between them and describe the cardinality of relationships.

Note: supertype, subtype and associative entities are necessary and must be used for this data modelling. For example, the common attributes (brand, model, colour etc) of a smart phone can be modelled into a supertype entity and the special attributes (connectivity, warranty etc) of new phones or those (data, call rates, weekly rent etc) of used phones can be modelled into 2 separate subtype entities. As another example, the bid for the listing of new phones can be modelled as an associative entity.