Introduction This assignment is expanded report which is essentially data base system nothing more more than than a compute computerize rized d
record records s
keeping keeping system. system. The
user of system system will be
given given facili facilitie ties s to perfor perform m a verif verify y of operat operation ions s data data record ecord such such as inser insertin ting, g, retrieving, retrieving, updating and deleting data. This reports will will be 4 tasks, •
•
•
Describe for given scenario of Hotel room booking bookin g and ritical review. review. Design Design a database via entity entity relatio relationship nship diagram diagram with !icrosoft !icrosoft "#$ server, %mpl %mplem eme entat ntatio ion n of "#$ "#$ ser server dat databas abase e
via DD$ DD$ and and D!$, D!$,
Demonstrate advance "#$ server features. Design database model , that use of !icrosoft !icrosoft "#$ server &''(,!icrosoft &''(,!icrosoft visual "tudio versions, versions , ) programming $anguage, $angu age, and *"+ *"+.net .net . This database was created with a future plan to use it online room booking system via via web web appl applic icati ation on.. This This sy syst stem em enhan enhanci cing ng stud study y enab enable led d cust custom omer ers s to book book reservations reservations for whatever system they want to place them where before lodging. The database will also provide large number of reports and information.
Case study 3.1. Case Study Scenario Hotel room booking is designed to hold information relating to a system. %t is needed to dened facts are are the re-uirements re-uirements which the database must meet and should agreed between database user and the database designer prior to physical creation. "ystem will facilities some of the below functions and more, more, •
Details of Hotel room facilities
oom types
oom facilities
1|+age
•
oom band
oom prices
ustomer and /uest details oom 0ooking details
Total Total rooms
0ooking room list
*vailable rooms
1eep track of the 0ookings +ayments Details
+ayment !ethods
Discount
* local hotel needs a database that keeps track of its booking current, future, achieved , rooms rooms and guest. Database will design design several several tables which is connected to each other base on the relationships.
Additional Considerations Additional 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 reservations for whatever system. They want to place then where before lodging. *dditionally this database will be supposed to implement the storage of guest details so that larger collection
Deliverables *dvanced *dvanced Databas Database e system system is %* ,that ,that regar regarded ded as a contr contributi ibution on to a my my personal personal developme development. nt. 0y develop developing ing this this assignment assignment , % have have achieve achieved d the below knowle knowledge dge areas.
3.3.1. Microsoft SQL Sever Database
2|+age
•
%nst %nstal allling
!icr !icro osof soft
"#$ "#$
"er "erver
&''(
manag anagem emen entt
stud studiio
successfully in my computer. •
$earn about 2ntity elationship elationship Diagrams.
•
$earn about 3ormalization.
•
reat reating ing databas database e to the
Hotel Hotel oom oom 0ooking 0ooking
with with functi functional onal
relationship in database including features for a real world. •
reating +artial +artial 2 diagram to understand and depicting of the room booking database.
•
reating tables.
•
reating +rimary 1eys.
•
reating foreign keys.
•
reating relationship between the tables reating onstraints Triggers. Triggers.
•
reating a stored procedures.
•
reating -ueries for the database to get re-uired re-uired details.
•
•
"how understand understanding ing of "#$ programm programming ing code such such as DD$ *3D D!$. Demonstrate advanced "#$ server features.
3.3.2. Other Deliverables 5hen asking -uestions of the database may need know, •
How many rooms currently available for booking6
•
5hat facilities are available in particular rooms6
•
5hich guests are booked in this week6
5.1. Full Entity Relation shi Dia!ra"
Fi!ure 1.# $ Full Entity Relation shi Dia!ra"
3|+age
5.5.1 Table – Customer 0elow are the screenshots of the D% &'(E) RESER*E . They will display the table design , data types of the eld ,primary key, 7oreign key, as well as constraints imposed. 2ach table design screenshot is followed with some sample data screen show.
5.5.2. All tables ith !"D Dia#rams 0elow is how the database looked liked inside the ob8ect 2xplorer panel under !icrosoft "#$ "erver &''(,management studio. •
•
•
7ive Database Diagram "eventeen Data tables 7ive 9iews :ne "tored +rocedures
+|+age
5|+age
Fi!ure ,.# D% &otelReserve inside the ob-ect elorer /anel
Fi!ure 0.#$ D% &otelReserve vie inside the ob-ect elorer anel
,|+age
5.5.3. Table – $uest 0elow will display the design of Table ustomer inside the ob8ect
2xplorer
panel, design window and sample data retrieved with a system generation D!$ statement.
Fi!ure .#$ Create table state"ent
5.5.%. Table $uest i& Ob'ect e()lorer
0|+age
Fi!ure .#$ (able 4uest in 'b-ect elorer
5.5.5. Table $uest Desi#& vie
Fi!ure 1#.#$ (able 4uest Desi!n vie
|+age
5.*.*. Table $uest Sam)le Data
Fi!ure 11.#$ (able Custo"er ith Data retrieved '(E $ Above screenshot o6 data is based on the S7) D8) state"ent !enerated by the syste".S7) state"ents ill be dislayed ith in this reort hen it co"es later toics.
0.0. Select (ables ith Condition 0elow "#$ code will display an instance where a table is selected view records. 0ut the records are ltered based on condition .
|+age
0.. Alter (ables9 Addin! Colu"n 6or the table 0elow "#$ will add a dummy column for the table.
0.. Delete Colu"ns 0elow "#$ ode will delete the column from a table.
0.1#. *ie :ith here clause 0elow is a bit advanced "#$ code which can lter the result .
1# | + a g e
0.11. Insert Data ithin a table
0.12. Create /ri"ary ;ey 0elow code will alter the table to add a primary key to the table guest.
0.13. Create Forei!n ;ey 0elow "#$ will display how a foreign key is created . this will connect to two tables.
11 | + a g e
0.1+. Create Constraints
0.1+. Create Stored /rocedures 0elow "#$ will create a stored procedure called booking
s!et%oo;in! to search
0.15. Create (ri!!er 0elow "#$ will show you a trigger created for the %3"2T active of 0ooking table.
12 | + a g e
0.1,. Create Inde 0elow Display create index in /uest table.
reate a my rst ""%" +ackage
reate new +ro8ect
13 | + a g e
Fi!ure 23.#. Create e /ro-ect
"elect %usiness Intelli!ence
"elect Inte!ration Services
"elect Inte!ration service ro-ect then
1+ | + a g e
'<.
Fi!ure 2+.#. Inte!ration Service ro-ect
15 | + a g e
Fi!ure 25.#. Create Data Flo (as;
2,.#. Create to Scrit (as; and Data =o (as;
1, | + a g e
Fi!ure 2,.#. Connectin! to (as;
%n this task add a ;at le connection !anager,
Fi!ure 2.# Con>!urin! Flat >le 8ana!er *dd and congure :$2 D0 connection manager
Fi!ure 2.#. ')E D% connection "ana!er *dding !ulticast
10 | + a g e
Fi!ure 3#.#. Addin! "ulticast onnecting to data ;ow tasks
Fi!ure 31.#. Connectin! to data =o tas;s 3ew data connection !anager
1 | + a g e
Fi!ure 32.# e Data Connection 8ana!er onnecting to simple database and setting to column
Fi!ure 33.# Connectin! to the (able and settin! to colu"n
hange the column in table
1 | + a g e
Fi!ure 3+.1. Chan!e the colu"n in table
Fi!ure 35.#. selectin! colu"n na"e
2# | + a g e
Fi!ure3,.#. ')E D% connection "ana!er
21 | + a g e
Fi!ure 30.#. Selectin! Server a"e
Fi!ure3.#. Create a (able
22 | + a g e
Fi!ure 3.#. Run Data =o (as;s
Fi!ure +#.#. *ie Data Sucess6ully 8i!ration
.1. 8icroso6t S7) Server Analysis Service9SSAS reating a sample database with !icrosoft "#$ server &'<4.%t will let us analyze data.
Fi!ure +1.# Select Analysis and Data Source *ies
23 | + a g e
Fi!ure +2.# Select a Data source
Fi!ure +3.# Data Connection Error
.2. S7) Server Reortin! Service9SSRS %t will let us create reports from analyze data and present it to end user.
2+ | + a g e
Fi!ure ++.# oen visual Studio
25 | + a g e
This error =and some other errors> cam repeatedly for various restore scenarios? % could not restore the database from .0*1 les@ although the backup le was created successfully? !ost online resource % referred didnAt work for me either?
2, | + a g e
Fi!ure +5 .# e /ro-ect
20 | + a g e
Fi!ure +,.# Select AD'.E( Entity Data 8odel
Fi!ure+0.# Choose 8odel Content
2 | + a g e
Fi!ure +.# Choose Data Connection
2 | + a g e
Fi!ure +.# Add Connection
Fi!ure 5#.# Choose Database 'b-ects
3# | + a g e
Fi!ure 51.# *ie a (ables
Fi!ure 52.# *ie 8odel Data
Fi!ure 53.# 7uery *ie in &otel Reserve Database
31 | + a g e
