Data base and design

 

1. Application Implementation

You Must write each of the following SQL commands:

Write a set of at least 6 SQL queries that would retrieve relative and important data from your database.

Write a set of at least 5 SQL updates that would modify relative and important data in your database.

Write a set of at least 5 SQL deletes that would delete some data from your database. Write a set of at least 2 SQL views that would define some virtual tables in your database.

 For each of the above SQL commands give a brief explanation of the purpose of each command and the output (if any) for each..Optional (extra credit):

Create forms, reports, queries, menus or navigation forms as needed on top of the tables. In general, a simple data entry form should be created for each table. For example, if the business takes orders from customers, create a form with Orders and Order items with

1. Write Up Final Report
   The final step is to prepare a formal report and brief presentation. This report should include:

   A separate cover page indicating the GROUP Number, the title of your project, the full names and IDs of the group members, the course number and course section.
   An introduction section similar to the proposal.
   Entity Relationship Model diagram.
   The collection of normalized relations and functional dependencies, and a brief discussion as to the normal form(s) achieved, the methods used to achieve these normal forms, and reasons why any de-normalization was done.
   The SQL DDL used to create the tables and add primary key and foreign key constraints.
   The SQL commands (as required in 5 above) along with their brief explanation of the purpose of each command and the output (if any) for each.
   Optional (extra credit) An example printout of each of the forms, reports and queries accompanied by a description of the function of each. Along with each form.
   A narrative conclusion section that describes:
   a) the group’s experience with the project (which steps were the most difficult? Which were the easiest ? what did you learn that you did not imagine you would have? if you had to do it all over again, what would you have done differently?)
   b) if the proposed benefits can be realized by the new system
   c) any final comments and conclusions

Description:

This project basically designs the database for hotel management system. There are different entities, attributes related to each entity and relationships between tables. There are many queries where we can find or retrieve the required information from a single or multiple table. This database is not that much advanced but can be one of the effective one in hotel management system.

Purposes:

The main Opportunity of this project is for online platform. Customers may have problem on making reservation for hotel rooms so customers can reserve or book the room as they want. Whenever the customer book the room, it will get update in reservation table. More, the Admin can run the queries and can see the status of their business. They will know the number of people book the room or anything they want by running specific query. Hence, this database will not only help in running queries but also helps in growing business.

Operational scenarios:

Operational Scenarios for our Hotel Management System is simple. There are six tables, and each table provides specific information of different sectors of the hotel management. When the customer shows up, the information of the customer is entering into the database like their name, their phone number, email, and other information’s. Once the information of the customer is entered then we ask for their choice of the room. we can assign them the room number. One for feature has been included that the customer can reserve the room in some other date and can check in another date.

List of tables:

· Costumer

· Today Price

· Room

· Hotel

· Employee

· Bill

Project Members & Responsibilities:

Name	Roles & Responsibilities
Sahadev Shrestha	Documentation Writer
Sangam Shrestha	System Analyst
Pranay Singh	Database Administrator

2. E-R Diagram:

Phase 2:

Changing ER-diagram into Relational Model

mysql command codes used;

Show databases; //show total database present

Use username_HotelMangementSystem; //using the database we need

Create table [Table Name]; // command for creating the tables that need there are Customer, Bill, TodayPrice, Room, Hotel and Employee tables.

Insert into [Table Name]; // command for inserting the values in the tables.

Select * from [Table Name]; //command for output of the values. This command provide the visual output of the inserted data.

Table:

Customer Table and Details:

Room Numbers:

Bill table:

Price table:

Hotel details:

Employee table and their details: