StudentShare
Contact Us
Sign In / Sign Up for FREE
Search
Go to advanced search...
Free

Database Management System - Statistics Project Example

Cite this document
Summary
The paper "Database Management System" presents that table schema for the flight booking company is given below in the table format. The constrains for a specific field are also given. In most of the fields, the not null integrity constraint is enforced…
Download full paper File format: .doc, available for editing
GRAB THE BEST PAPER91.2% of users find it useful
Database Management System
Read Text Preview

Extract of sample "Database Management System"

Database Management Systems The table schema for the flight booking company are given below in the table format. The constrains for a specific fieldis also given. In most of the fields the not null integrity constraint is enforced. The relevant primary key and foreign key are also enforced properly. The data type for a particular field is choose with respect to the context of the field and most of the fields the data length are specified in par with the real time data. For example the number of flights is limited to 6 digit and number of seats is limited to 4 digit. The varchar2 data type is used in many cases which is advantageous in terms of memory management and also to make changes in the size of a field in future as required. Table Name: CUSTOMER Attribute Name Data Type Constrains Customer_no number(12) Primary Key Customer_town varchar2(30) Not Null Customer_name varhchar2(45) Not Null Customer_address varchar2(80) Not Null SQL query: create table Customer(Customer_no number(12) constraint CONST_PK_CUST primary key not null, Customer_town varchar2(30) not null, Customer_name varchar2(45) not null, Customer_address varchar2(80) not null ); Table Name: SALES_REP Attribute Name Data Type Constrains Sales_rep_id number(1) Primary key, Range in (1 to 5) Sales_rep_name varchar2(45) Not null SQL query: create table Sales_rep(Sales_rep_id number(1) constraint CONST_PK_CUST primary key constraint check_id check(Sales_rep_id between 1 to 5), Sales_rep_name varchar2(45) not null); Table Name: FLIGHT Attribute Name Data Type Constrains Flight_code varhchar2(6) Primary key Departure_day_time date Not null Departure_airport varhcar2(30) Not null Arrival_time date Not null Arrival_airport varchar2(30) Not null SQL query: create table Flight(Flight_code varchar2(6) constraint CONST_PK_FLIGHT primary key, Departure_day_time date not null, Departure_airport varchar2(30) not null, Arrival_time date not null, Arrival_airport varchar2(30) not null); Table Name: BOOKING Attribute Name Data Type Constrains Booking_ref_no number(10) Primary key Booking_date Date Not null Customer_no number(10) References Customer_no of Customer Deposit_paid varchar2(1) Not null, check in Y,N Sales_rep_id Number(1) References Sales_rep_id of Sales_rep SQL Query: create table Booking(Booking_ref_no number(10) constraint CONST_PK_BOOKING primary key, Booking_date date, Customer_no number(10) constraint CONST_FK_BOOKING references Customer(Customer_no), Deposit_paid varchar2(1) not null constraint CHECK_YN check(Deposit_paid in(Y,N)), Sales_rep_id number(1) constraint CONST_FK2_BOOKING references Sales_rep(Sales_rep_id) ); Table Name: SEAT Attribute Name Data Type Constrains Seat_no number(5) Primary key Class_code bool References class_code of Class SQL Query create table Seat(Seat_no number(5) constraint CONST_PK_SEAT primary key, Class_code bool constraint CONST_FK_SEAT references Class(Class_code)); Table Name: CLASS Attribute Name Data Type Constrains Class_code bool Primary key Class varchar2(7) Not null SQL query: create table Class(Class_code bool, Class varchar2(7) not null); Table Name: FLIGHT_SEAT Attribute Name Data Type Constrains Flight_code varchar2(6) Primary key, References Flight_code of Flight Seat_no Number(4) Primary key, References Seat_no of Seat Price number(5) Not null SQL Query create table FLIGHT_SEAT(Flight_code varchar2(6) constraint CONST_FK1_FLIHTSEAT references Flight(Flight_code), Seat_no number(4) constraint CONST_FK1_FLIGHTSEAT references Seat(Seat_no), price number(5) not null,constraint CONST_PK1_FLIGHTSEAT primary key(Flight_code,Seat_no)); Table Name: BOOKING_LINE Attribute Name Data Type Constrains Booking_ref_no number(10) References Booking_ref_no of BOOKING Flight_code varchar2(6) Primary key, References flight code and Seat_no of FLIGHTSEAT Seat_no Number(5) Flight_date Date Primary key Passenger_surname Varchar2(20) Not null SQL query: create table Booking_line(Booking_ref_no number(10) constraints CONST_FK1_BOOKINGLINE references Booking_line(Booking_ref_no), Flight_code varchar2(6), Flight_date date,Passenger_surname varhcar2(20), constraints CONST_PK_BOOKINGLINE primary key (Flight_code, Seat_no, Flight_date),constraints CONST_FK2_BOOKINGLINE foreign key (Flight_code, Seat_no) references Flight_seat(Flight_code, seat_no) ); The entity SEAT seems to be a over head in data entry and also it has some effect in database operation. The SEATNO and CLASS_CODE field in the SEAT entity can be moved into the FLIGHT_SEAT entity. In the FLIGHTSEAT entity we can have the CLASS_CODE which will solve the above issues. The updated FLIGHT_SEAT entity is given below. Table Name: FLIGHT_SEAT Attribute Name Data Type Constrains Flight_code varchar2(6) Joined Primary key Seat_no Number(4) Price number(5) not null Class_code bool References class_code of CLASS SQL query: Create table Flight_seat(Flight_code number(6), Seat_no number(4), Price number(5), Class_code bool); When the entity FLIGHT_SEAT is inserted a new row or updated any existing row the CLASS field should be first class if the seat no is less than 21 otherwise it should be set to economy class. To handle this business rule a trigger is written to check if the CLASS filed in the FLIGHT_SEAT entity whenever an insert or update happens to the entity. The trigger checks for the rule and it automatically assigns the right class code based on the seat no thus achieving efficiency in the data entry. The given below is the required trigger. Trigger1.sql Create or replace trigger set_class after insert or update of Seat_no,Class_code on Flight_seat for each row declare seatno number(5); classcode bool; classname varchar2(7); begin select f.Seat_no, f.Class_code, c.Class into seatno,classcode,classname from Flight_seat f,Class c where Flight_code = :new.Flight_code and f.Class_code = c.Class_code; if (:new.Seat_no < 21 and :new.Seat_no >0 and :new.Class_code = FALSE) then raise_application_error(-10000,Wrong Class Code || classname); :new.Class_code = TRUE; elsif(:new.Seat_no>20 and :new.Class_code = TRUE) raise_application_error(-10001,Wrong Class Code || classname); :new.Class_code = FALSE; end if; end; Inserting the given data: insert into Customer values(1, Windsor, RHM PLC, Windsor House Ascot Road); insert into Sales_rep values (1, john smith); insert into Flight values(KN456, to_date(Wed 12:00,dy:hh:mi),Gatwick, to_date(hh:mi,14:00), Athens); insert into Flight values(KN457, to_date(Fri 11:00,dy:hh:mi),Athens, to_date(hh:mi,12:00), Gatwick); insert into Booking values(1,to_date(dd:mon:yyyy,14:Jan:2010),1,Y,1); insert into Flight_seat values(KN456, 112, 110, 0); insert into Flight_seat values(KN456, 2, 150, 1); insert into Flight_seat values(KN457, 120, 110, 0); insert into Flight_seat values(KN457, 2, 150, 1); insert into Booking_line values(1,KN456,112,to_date(dd:mm:yyyy,14:07:2010), Patel); insert into Booking_line values(1,KN456,2,to_date(dd:mm:yyyy,14:07:2010), Khan); insert into Booking_line values(1,KN456,112,to_date(dd:mm:yyyy,21:07:2010), Smith); insert into Booking_line values(1,KN457,120,to_date(dd:mm:yyyy,16:07:2010), Jones); insert into Booking_line values(1,KN456,2,to_date(dd:mm:yyyy,16:07:2010), Khan); Report to list all booking made by a sales representative: The report can be achieved by using procedure so that we can have a formatted output. We will get the Sales representative id and the we will display the report. The required procedure is as follows: Create or replace procedure booking_report (Salesrepid IN number(2)) IS Cursor BookingLine IS select flight_code, passenger_surname,Sales_rep_id from Booking_Line; CountPass, CountPassFlight number(7); booking_line_rec BookingLine%rowtype; Salesrepname varchar2(30); BEGIN select count(passenger_surname) into CountPass from booking_line where sales_rep_id = Salesrepid; select sales_rep_name from sales_rep into Salesrepname where sales_rep_id=salesrepid; dbms_output.put_line( Sales Rep Name || Salesrepname||Passenger Count || CountPass); IF NOT BookingLine%ISOPEN then open BookingLine END IF dbms_output.put_line( FLIGHT_CODE || PERSON_PER_FLIGHT || PASSENGER_SURNAME ); FETCH BookingLine INTO booking_line_rec; EXIT WHEN booking_line_rec%NOTFOUND; select count(passenger_surname) from booking_line group by passenger_surname having flight_code = booking_line_rec.flight_code; select count(passenger_surname) into CountPassFlight from booking_line where sales_rep_id = Salesrepid and flight_code=BookingLine_rec.flight_code; IF( booking_line_rec.Sales_rep_id = Salesrepid) then dbms_output.put_line( booking_line_rec.flight_code || || || CountPassFlight || booking_line_rec.passenger_surname ); END IF END LOOP; END; The Output of the above report: SQL>exec booking_report (1) Sales Rep Name john smith Passenger Count 5 FLIGHT_CODE PERSON_PER_FLIGHT PASSENGER_SURNAME KN456 3 Patel KN456 3 Khan KN456 3 Smith KN457 2 Jones KN457 2 Khan Work Cited Connor McDonald, Chaim Katz, Christopher Beck, Joel R.Kallaman. “Mastering Oracle: PL/SQL: practical Solutions”. Apress, 2004 Oracle® Database. Data Warehousing Guide 10g Release 2 (10.2) B14223-02, December 2005 http://download.oracle.com/docs/cd/B19306_01/server.102/b14223.pdf Read More
Cite this document
  • APA
  • MLA
  • CHICAGO
(Database Management System Statistics Project Example | Topics and Well Written Essays - 1000 words - 2, n.d.)
Database Management System Statistics Project Example | Topics and Well Written Essays - 1000 words - 2. https://studentshare.org/management/1573455-database-management-system
(Database Management System Statistics Project Example | Topics and Well Written Essays - 1000 Words - 2)
Database Management System Statistics Project Example | Topics and Well Written Essays - 1000 Words - 2. https://studentshare.org/management/1573455-database-management-system.
“Database Management System Statistics Project Example | Topics and Well Written Essays - 1000 Words - 2”. https://studentshare.org/management/1573455-database-management-system.
  • Cited: 0 times

CHECK THESE SAMPLES OF Database Management System

Creating a Database Management System: Mountain Bike Rentals

This paper focuses on the gaps in the scientific comprehension of recreation effects, inform natural resource managers in the construction of database management systems of rental mountain bikes, and as well offer a technical advice for maintainable riding for mountain riding devotees and associations.... The study focuses on the gaps in the scientific comprehension of recreation effects, inform natural resource managers in the construction of database management systems of rental mountain bikes, and as well offer a technical advice for maintainable riding for mountain riding devotees and associations....
6 Pages (1500 words) Research Paper

Database Management System for the Fitness Centre

In the paper “Database Management System for the Fitness Centre” the author provides a database system.... hellip; The author states that the database for the management system should be designed in the Third Normal Form.... which is intended to track people's participation in various fitness activities....
1 Pages (250 words) Case Study

Database Management System - Fitness Club

From the paper "Database Management System - Fitness Club " it is clear that Fitness Club is an organization's effort aimed at boosting its staff's job performance by providing them with a health and fitness service to release stress.... To summarize, the current manual club management system is unable to provide an insight into the intricate financial queries that can ascertain the financial costs and identify areas of the club's financial growth....  System CapabilitiesFitness Club management system aims to provide the following system-level capabilities;• Manage Classeso System will store the detailed outline of the various classes (Zumba, Salsa, Combat, Body Conditioning, Pilates, Yoga etc....
7 Pages (1750 words) Case Study

Components of a Database Application, Database Management System

The paper "Components of a Database Application, Database Management System" states that Samantha could easily implement MySQL.... Since most database functions performed by Samantha would be simple, a single user personal Database Management System (DBMS) would be a good choice.... hellip; Information regarding the customer and the job would have to be entered and monitored in a database by a single individual managing the company, which is Samantha....
1 Pages (250 words) Assignment

Managing Information for Competitive Advantages

Explanations of database and Database Management System are also provided in the paper.... The information system of a particular organization would include various technologies, manpower, and several other components.... This paper would include a detail discussion of an information system and its various components.... The paper would also include the importance of the information system in the business organization.... Generally, the Information system is the system which, include records of data, people and all those activities of processing of this information....
8 Pages (2000 words) Research Paper

IPR Law Firm's Distributed Database Management System

This paper aims to study the architecture and requirements of the distributed Database Management System of an Intellectual Property Rights Law firm in the United States.... The paper identifies the problems in the existing architecture and recommends solutions to overcome them  … The distributed Database Management System of an IPR law firm is a client/server and 2-tier structure.... Furthermore, the distributed Database Management System acts as a single application that has multiple databases working for it....
10 Pages (2500 words) Term Paper

Database Buyer's Guide Conclusion from Database Journal

On the other hand, a Database Management System refers to the computer software used to provide interfacing to users when they access databases and database systems on computers.... A Database Management System is the shell that surrounds databases and allows for interactions to the databases.... From the pros and the cons, the customers are able o gauge which relational Database Management System best suits their needs as well as preferences as far as the Database Management System is concerned....
3 Pages (750 words) Research Paper

Broadway Training Partners New Training Centre

The project scope and deliverables include an IT architecture plan; hardware, software, Database Management System, infrastructure purchase, and security solution purchase and their installation; transfer of existing business applications and data; staff and user training; test and evaluation, and going live with the full system.... The new system and network will allow BTP to double the courses run yearly and support 10 more major corporate clients.... Maintenance of the system and network, refurbishment of the training center, and availing of training and breakout rooms are out of scope for this sub-project....
6 Pages (1500 words) Report
sponsored ads
We use cookies to create the best experience for you. Keep on browsing if you are OK with that, or find out how to manage cookies.
Contact Us