Database Design - I

Posted on 20th Jan 2017

<-Back to Blogs

The process of doing database design generally consists of a number of steps which will be carried out by the database designer. Usually, the designer must:

• Determine the data to be stored in the database.
• Determine the relationships between the different data elements.
• Superimpose a logical structure upon the data on the basis of these relationships.

Requirements specification -> Requirement Analysis -> Conceptual design (Entity-Relationship Model )-> Logical Schema Design/Implementation Design(Database Schema)-> Physical Schema Design(Access Paths)-> Optimisation

Requirement Analysis:

• Customer communication!
• Identify essential "real world" information
• Remove redundant, unimportant details
• Clarify unclear natural language statements
• Fill remaining gaps in discussions
• Distinguish data and operations

For example: The business is a Dainty Meal(DaMe) company. The business provides it's services by providing their customers:

1. A list of the names of restaurant in the city on basis of customer choices- a) City Name b) Cuisines Type
2. A list of specially customized gourmet menu when the customer choose a restautrant and meal courses offered by that restaurant.
3. Book a dining table service facility which notifies the customer & DaMe whether the restaurant has seat availabilty or not.
4. Feedback from the customer about their experience for the above availed service.  

In order to facilitate these services above, DaMe company had tie up with many restaurants in different cities. DaMe surveys the restaurant and collect information (text and picture)like-

1. What are the cuisines the restaurant offers?
2. How many customers per dinning table they can accomodate?
3. Opening and Closing time of the restaurant?

Conceptual design:

Once all the requirements have been collected and analyzed, the next step is to create a conceptual shema for the database, using a high level conceptual data model. This phase is called conceptual design.

The result of this phase is an Entity-Relationship (ER) diagram or UML class diagram. It is a high-level data model of the specific application area. It describes how different entities (objects, items) are related to each other. It also describes what attributes (features) each entity has. It includes the definitions of all the concepts (entities, attributes) of the application area.

During or after the conceptual shema design, the basic data model operations can be used to specify the high-level user operations identified during the functional analysis. This also serves to confirm that the conceptual schema meets all the indenfied functional requirements. There are several notations to draw the ER diagram.

The requirements document can then be analysed and turned into a basic data set (as shown below) which can be converted into a conceptual model. The end result of the conceptual design phase is a conceptual data model (Figure 1), which provides little information of how the database system will eventually be implemented. The conceptual data model is simply a high-level overview of the database system.

 Let's next see how we make a conceptual design from the above analyzied requirements:(We had tried to break the en)

1. Entity : 
   1. People : customer
   2. Things: dinning_table, cuisine
   3. Events: order, order_item, order_state
   4. location: restaurant, restaurant_account
2. Attribute:
   1. customer- cust has:
      1. cust_id (Primary Key)
      2. cust_name
      3. cust_phoneno
      4. cust_password
      5. cust_push_id
      6. cust_joined_time
   2. dinning_table- tbl has:
      1. tbl_id (Primary Key)
      2. tbl_rest_id (Foreign Key rest_id)
      3. tbl_table_number
      4. tbl_capacity
   3. cuisine has:
      1. c_id(Primary Key)
      2. c_rest_id(Foreign Key rest_id)
      3. c_name
      4. c_price
      5. c_pic
   4. order has:
      1. o_id(Primary Key)
      2. o_rest_id(Foreign Key rest_id)
      3. o_cust_id(Foreign Key cust_id)
      4. o_total_price
      5. o_num_people
      6. o_request_date
      7. o_start_time
      8. o_end_time
      9. o_table_number
      10. o_status (Foreign Key )
      11. o_created_time
      12. o_updated_time
   5. order_item has:
      1. oitem_id (Primary Key)
      2. oitem_order_id(Foreign Key o_id)
      3. oitem_name
      4. oitem_price
      5. oitem_quantity
   6. restaurant has:
      1. rest_owner_id (Foreign Key ra_id)
      2. rest_name
      3. rest_address
      4. rest_geo_location
      5. rest_pic
      6. rest_pic_thumb
      7. rest_id (Primary Key)
   7. restaurant_account has:
      1. ra_id(Primary Key)
      2. ra_name
      3. ra_password
      4. ra_created_time
3. Relationship is between Entities.
   1. cust -> restaurant (customer selects a restaurant)
   2. cust -> cuisine (customer selects a cuisine)
   3. cust -> order (customer places an order for dining table)
   4. restaurant -> order (restaurant operates on the orders) and so forth

(This post is still in progress as the author is busy in some other stuffs :P. Regret for inconvience. Would finish it sooner as possible)