We Have Numbers Of Free Samples

For Each Subject To Make A Difference In Your Grade

MySQL Database Project

Total Views 707

Introduction

This assignment is expanded report which is essentially data base system nothing more than a computerized records keeping system. The user of system will be given facilities to perform a verify of operations data record such as inserting, retrieving, updating and deleting data.

This reports will be 4 tasks,

Describe for given scenario of Hotel room booking and Critical review.
Design a database via entity relationship diagram with Microsoft SQL server,
Implementation of SQL server database via DDL and DML, • Demonstrate advance SQL server features.

Design database model , that use of Microsoft SQL server 2008,Microsoft visual Studio versions, C# programming Language, and ASP.net .

This database was created with a future plan to use it online room booking system via web application. This system enhancing study enabled customers to book reservations for whatever system they want to place them where before lodging. The database will also provide large number of reports and information.

Introduction

3.1. Case Study Scenario

Hotel room booking is designed to hold information relating to a system. It is needed to defined facts are the requirements which the database must meet and should agree between database user and the database designer prior to physical creation. System will facilities some of the below functions and more,

Details of Hotel room facilities
1. Room types
2. Room facilities
3. Room band
4. Room prices
Customer and Guest details
- Room Booking details
  1. Total rooms
  2. Booking room list
  3. Available rooms
  4. Keep track of the Bookings
Payments Details
1. Payment Methods
2. Discount

A local hotel needs a database that keeps track of its booking current, future, achieved , rooms and guest. Database will design several tables which is connected to each other base on the relationships.

Additional Considerations

This database will be implementing with a future plan to use it online room booking system via web application. This system enhancing study enabled customers to book reservations for whatever system. They want to place then where before lodging.

Additionally, this database will be supposed to implement the storage of guest details so that larger collection.

3.3.1. Microsoft SQL Sever Database

Installing Microsoft SQL Server 2008 management studio successfully in my computer.
Learn about Entity Relationship Diagrams.
Learn about Normalization.
Creating database to the Hotel Room Booking with functional relationship in database including features for a real world.
Creating Partial ER diagram to understand and depicting of the room booking database.
Creating tables.
Creating Primary Keys.
Creating foreign keys.
Creating relationship between the tables  Creating Constraints & Triggers.
Creating stored procedures.
Creating queries for the database to get required details.
Show understanding of SQL programming code such as DDL AND DML.
Demonstrate advanced SQL server features.

3.3.2. Other Deliverables

When asking questions of the database may need know,

How many rooms currently available for booking?
What facilities are available in particular rooms?
Which guests are booked in this week?

5.1. Full Entity Relationship Diagram

5.5.1 Table – Customer

Below are the screenshots of the DB HOTEL RESERVE. They will display the table design, data types of the field, primary key, Foreign key, as well as constraints imposed. Each table design screenshot is followed with some sample data screen show.

5.5.2. All tables with ERD Diagrams

Below is how the database looked like inside the object Explorer panel under Microsoft SQL Server 2008, management studio.

Five Database Diagram
Seventeen Data tables
Five Views
One Stored Procedures

Figure 6.0 DB HotelReserve inside the object explorer Panel

Figure 7.0: DB HotelReserve view inside the object explorer panel

5.5.3. Table – Guest

Figure 8.0: Create table statement

5.5.4. Table Guest in Object Explorer

Figure 9.0: Table Guest in Object explorer

5.5.5. Table Guest Design View

Figure 10.0: Table Guest Design view

5.6.6. Table Guest Sample Data

Figure 11.0: Table Customer with Data retrieved

NOTE: Above screenshot of data is based on the SQL DML statement generated by the system. SQL statements will be displayed with in this report when it comes later topics.

7.7. Select Tables with Condition

Below SQL code will display an instance where a table is selected view records. But the records are filtered based on condition.

7.8. Alter Tables- Adding Column for the table

Below SQL will add a dummy column for the table.

7.9. Delete Columns

Below SQL Code will delete the column from a table.

7.10. View with where clause

Below is a bit advanced SQL code which can filter the result.

7.11. Insert Data within a table

7.12. Create Primary key

Below code will alter the table to add a primary key to the table guest.

7.13. Create Foreign key

Below SQL will display how a foreign key is created, this will connect to two tables.

7.14. Create Constraints

7.14. Create Stored Procedures

Below SQL will create a stored procedure called spgetBooking to search booking

7.15. Create Trigger

Below SQL will show you a trigger created for the INSERT active of Booking table.

7.16. Create Index

Below Display create index in Guest table.

Create a my first SSIS Package
Create new Project

Figure 23.0. Create New Project

Select Business Intelligence
Select Integration Services
Select Integration service project then OK.

Figure 24.0. Integration Service project

Figure 25.0. Create Data Flow Task

26.0. Create to Script Task and Data flow Task

Figure 26.0. Connecting to Task

In this task add a flat file connection Manager,

Figure 28.0 Configuring Flat file Manager

Add and configure OLE DB connection manager

Figure 29.0. OLE DB connection manager

Adding Multicast

Figure 30.0. Adding multicast

Connecting to data flow tasks

Figure 31.0. Connecting to data flow tasks

New data connection Manager

Figure 32.0 New Data Connection Manager

Connecting to simple database and setting to column

Figure 33.0 Connecting to the Table and setting to column

Change the column in table

Figure 34.1. Change the column in table

Figure 35.0. selecting column name

Figure36.0. OLE DB connection manager

Figure 37.0. Selecting Server Name

Figure38.0. Create a Table

Figure 39.0. Run Data flow Tasks

8.1. Microsoft SQL Server Analysis Service-SSAS

Creating a sample database with Microsoft SQL server 2014.It will let us analyze data.

Figure 41.0 Select Analysis and Data Source Views

Figure 42.0 Select a Data source

Figure 43.0 Data Connection Error

8.2. SQL Server Reporting Service-SSRS

It will let us create reports from analyze data and present it to end user.

Figure 44.0 open visual Studio

This error (and some other errors) cam repeatedly for various restore scenarios! I could not restore the database from .BAK files; although the backup file was created successfully! Most online resource I referred didn’t work for me either!