We use cookies to give you the best experience possible. By continuing we’ll assume you’re on board with our cookie policy

See Pricing

What's Your Topic?

Hire a Professional Writer Now

The input space is limited by 250 symbols

What's Your Deadline?

Choose 3 Hours or More.
Back
2/4 steps

How Many Pages?

Back
3/4 steps

Sign Up and See Pricing

"You must agree to out terms of services and privacy policy"
Back
Get Offer

Advanced Database Systems

Hire a Professional Writer Now

The input space is limited by 250 symbols

Deadline:2 days left
"You must agree to out terms of services and privacy policy"
Write my paper

KINGSTON UNIVERSITY Faculty of Science, Engineering and Computing CI 3116 – Advanced Database Systems DESIGN EXERCISE FINAL REPORT Daffodil Theatre Booking System Gabriel Delucis Hilal K0935109 15th May 2012 Table of Contents Introduction1 Aim1 Objectives1 Conceptual Data Model1 Relational Data Model1 Implementation1 Conceptual Data Model2 Class Diagram2 Descriptions and Assumptions2 Discussion4 Relational Data Model5 Relational Data Model Diagram5 Data Dictionary5 Discussion8 Implementation9 Table Listing9 staff9 productions9 prices_level9 seats9 productions_prices9 erformances10 customers10 cards10 bookings10 tickets11 members11 duties11 members_duties12 performances_duties12 Discussion13 Conclusion14 Appendix A – Queries for the Coursework Demonstration15 Query 1 – Details of each scheduled production (name, date, time, tickets available and prices)15 Query 2 – The management of ticket sales16 Query 3 – The production of a report detailing the customers with reserved tickets awaiting to be collected for a particular performance (tickets to be printed)17 Query 4 – The production of a summary of ticket sales for a particular production18

Query 5 – Details of the volunteers required for each performance of a production19 Query 6 – Details of when volunteers are available to help and the duties they can do19 Query 7 – Details of the volunteers who have been assigned to duties for a performance20 Appendix B – Constraints for the Coursework Demonstration21 Constraint 1 – NOT NULL21 Constraint 2 – CHECK21 Constraint 3 – UNIQUE21 Constraint 4 – DATA TYPE22 Constraint 5 – INTEGRITY22 Introduction The Daffodil Theatre is a small provincial theatre, which has received funds to finance the building of a new large auditorium.

Don't use plagiarized sources. Get Your Custom Essay on
Advanced Database Systems
Just from $13,9/Page
Get custom paper

The money will also be used to develop a computerised booking system to manage the whole process, from seat reservation to profit reports.

In order to develop the request Daffodil Theatre Booking System, the assignment description was carefully read to ensure that all requirements were fulfilled. However, some assumptions have been taken to fill gaps where requests were not specified, allowing the achievement of a comprehensive system as reported in this document. Aim The aim of this report is to describe the design decision, as well as any assumption taken during the different stages of the system’s development.

Objectives The reported is divided in three main sections: Conceptual Data Model, Relational Data Model and Implementation. Each section has the following objectives. Conceptual Data Model * Identify the needed classes for the system; * Recognise the relationships between the found classes; * Solve any many-to-many association that might resulted; * Describe any assumption taken while identifying classes; * Discuss the design decisions when developing the conceptual model. Relational Data Model * Convert classes into tables; Identify the primary key within the existing attributes; * Add or remove attributes if necessary; * Recognise the associations between primary and foreign keys; * Describe any assumption taken when converting classes into tables; * Define the data type required for each attribute; * Identify the required constraints for each attribute; * Discuss the design decisions when developing the relational data model. Implementation * Identify the chosen data types within the available Oracle data types; * Change the data types to be in accordance to Oracle; Implement the tables according to the relational data model; * Describe any assumption taken when implementing the tables; * Discuss the design decisions when converting the relational data model into Oracle CREATE TABLE statements. Conceptual Data Model Class Diagram Descriptions and Assumptions Entity| Attributes| Description| Assumptions| Staff| name| Staff Name| Class not clearly required, but staffs are necessary if there are not enough volunteers to do all duties in a specific performance. | | address| Staff Address| | telephone| Staff Telephone| | | email| Staff Email| | Production| name| Production Name|

Based on the figure 2 in the assignment description. No assumptions were need. | | type| Type of Entertainment| | | description| Production Description| | Seat| number| Seat Number| Based on the figure 1 in the assignment description, these three attributes were recognised as essential to identify a seat. | | row| Row | | | block| Block | | Price Level| level| Price Level| Four price levels required, but this entity allows the creation of many levels. | description| Level Description| | Production Price| price| Price according to the combination of Performance and Price Level. | Association class to solve the many-to-many relationship between Performance and Price Level. | Performance| date| Performance Date| Based on the figure 2 in the assignment description. No assumptions were need. | | time| Performance Time| | Customer| name| Customer Name| Based on figure 3, in the assignment description, the attributes were clearly identified. No assumptions were need. | | address| Customer Address| | telephone| Customer Telephone| | | email| Customer Email| | Card| number| Card Number| The assignment’s description states that all major credit cards are accepted, but it doesn’t specify the details necessary to charge payments. We have assumed that the chose attributes are the needed information to take payments from cards. | | type| Card Type (Visa, Amex, etc)| | | start date| Card Start Date| | | expire date| Card Expire Date| | | cvc| Card Security Code| | | address| Billing Address| | Booking| type| Booking Type (telep. post, etc)| The attributes were based on the figure 3, in the coursework specification. | | payment type| Payment Type (cash, card, etc)| | Ticket| concession| If student or senior citizens| Based in figure 3, the concession attribute was identified. This class will also solve the many-to-many relationship between Performance and Seat. | Member| Monday| Available to work on Mon| Based on the figure 4, in the assignment specification, this class will keep the information about members’ availability to assist at performances. Furthermore, we have assumed that all members are also customers.

In this way all the member details are kept on the Customer class, such as name, address, email and telephone. | | Tuesday| Available to work on Tue| | | Wednesday| Available to work on Wed| | | Thursday| Available to work on Thu| | | Friday| Available to work on Fri| | | Saturday Matinee| Available to work on Sat Matinee| | | Saturday Evening| Available to work on Sat Evening| | | Sunday| Available to work on Sun| | Duty| name| Duty Title| Class was based on the figure 4, in the coursework, where the duties are listed. | | description| Duty Description| |

Member/Duties| | | This class will solve the many-to-many relationship between Member and Duty, resulting in a list of members and the duties they are able to do. | Performance/ Duties| | | This class will solve the many-to-many relationship between Performance and Duty, resulting in a list of needed duties in a performance. Also the members or staff allocated to each duty is defined here. | Discussion The class Staff was not a requirement stated in the assignment’s description. In order to keep the ticket prices lower, the Daffodil Theatre relies on volunteers to assist at performances.

However, if there are not enough volunteers to work in a specific event, we have assumed that staffs will complete the team to ensure sufficient helpers for all required duties. The class Seat was created according to the seating plan image available in the coursework, figure 1. The seats are identified by its number, row and block. Furthermore, four price levels were required, which was achieved through the class Price Level. Actually, this entity allows, not only the four needed four levels, but many other price levels can be created in the future.

The classes Production and Performance were based on the figure 2, in the assignment’s description, where the attributes the needed attributes clearly defined. Moreover, the coursework states that the price must be related to the production, which brings us to a many-to-many relationship where the Production can have many Price Levels and the same Price Level can be used by many Productions. The association class Production Price overcame this problem storing the price according to the Performance and Price level.

Based on figure 3, in the coursework specification, we have identified the need of Customer and Booking classes. Furthermore, we have assumed that a Customer can make many Bookings, which could be paid using different cards. For this reason, we have created the class Card instead of having the card details inside the class Customer. Another many-to-many relationship was identified between Seat and Performance, as the same seat will be used for many performances, while the same performance has many seats.

In order to solve this relationship, we have defined a new association class called Ticket. Furthermore, we have assumed that a Booking can contain many Tickets, which is the combination of seat and performance. As per the figure 3, in the coursework specification, senior citizens and students will have discount in the ticket price, so the attribute concession was included in the class Ticket. We have also assumed that a Member must be a Customer, in this way only the days that member are available to work will be kept in the class Member.

Other information such as name, email, telephone and address can be retrieved from the class Customer. Based on the figure 4 in the coursework, the needed of a class Duty was also recognised. This entity will keep the list of possible duties as well as its description. Furthermore we have identified that one Member might be able to do many Duties, while the same Duty can also be done by many Member, which brings us to another many-to-many relationship. The association class Member/Duties will solve this relation, keeping a list of members and the duties they are able to do.

Finally, the class Performance/Duties will solve the many-to-many relationship between Performance and Duty, as each performance will have many Duties and the same Duty will be needed by many other Performances. This class will also be related to the Member/Duties, which allows the needed duties to be allocated to Members that are able to do that Duty. Also, Performance/Duties is related to the class Staff, allowing unallocated Duties to be done by Staffs, ensuring sufficient helpers for all required Duties in a specific Performance. Relational Data Model Relational Data Model Diagram Data Dictionary

Table| Attributes| Type| Key and Constraints| Description| staff| StaffId| number(5,0)| Primary Key| Staff Id| | StaffName| varchar2(20)| NOT NULL| Staff Name| | Address| varchar2(40)| | Staff Address| | Telephone| varchar2(10)| | Staff Telephone| | Email| varchar2(30)| | Staff Email| productions| ProductionName| varchar2(30)| Primary Key| Product. Name| | Type| varchar2(20)| CHECK (Type IN ‘Classical Music’,’Drama’,’Jazz’,’Ballet’,’Comedy’)| Type of Entert. | | Description| varchar2(350)| | Product. Descr. | seats| SeatNumber| number(2,0)| Primary Key| Seat Number| | SeatRow| varchar2(1)| Primary Key| Row | LevelId| varchar2(1)| Primary KeyForeign Key [price_level(LevelId)]| Level Id alike Block| prices_level| LevelId| varchar2(1)| Primary Key| Price Level| | Description| varchar2(125)| | Level Descrip. | production_prices| ProductionName| varchar2(30)| Primary KeyForeign Key [productions(ProductionName)]| Product. Name| | LevelId| varchar2(1)| Primary KeyForeign Key [price_level(LevelId)]| Price Level| | Price| number(4,2)| NOT NULL| Product. Price| performances| PerformanceDate| date| Primary Key| Perform. Date| | PerformanceTime| varchar2(5)| Primary Key| Perform.

Time| | ProductionName| varchar2(30)| NOT NULLForeign Key [productions(ProductionName)]| Product. Name| customers| CustomerId| number(10,0)| Primary Key| Customer Id| | CustomerName| varchar2(20)| NOT NULL| Customer Name| | Address| varchar2(40)| | Custom. Addr. | | Telephone| varchar2(10)| | Custom. Teleph. | | Email| varchar2(30)| | Customer Email| cards| CardNo| number(20,0)| Primary Key| Card Number| | Type| varchar2(16)| CHECK (Type IN ‘Visa’,’Maestro’,’Mastercard’,’Amex’)| Card Type| | StartDate| date| | Start Date| | ExpireDate| date| NOT NULL| Expire Date| | CVC| number(3,0)| NOT NULL| Security Code| Address| varchar2(40)| | Billing Address| | CustomerId| number(10,0)| NOT NULLForeign Key [customers(CustomerId)]| Customer Id| bookings| BookingId| number(10,0)| Primary Key| Booking Id| | Type| varchar2(9)| CHECK (Type IN ‘Telephone’,’In Person’,’By Post’)| Booking Type| | PaymentType| varchar2(11)| CHECK (PaymentType IN ‘Credit Card’,’Debit Card’,’Cash’,’Cheque’)| Payment Type| | CardNo| number(20,0)| Foreign Key [cards(CardNo)]| Card Number| | CustomerId| number(10,0)| NOT NULLForeign Key [customers(CustomerId)]| Customer Id| tickets| PerformanceDate| date| Primary KeyForeign Key [performances(PerformanceDate)]| Perform.

Date| | PerformanceTime| varchar2(5)| Primary KeyForeign Key [performances(PerformanceTime)]| Perform. Time| | LevelId| varchar2(1)| Primary KeyForeign Key seats(LevelId)]| Level Id| | SeatRow| varchar2(1)| Primary KeyForeign Key [seats(SeatRow)]| Row | | SeatNumber| number(2,0)| Primary KeyForeign Key [seats(SeatNumber)]| Seat Number| | BookingId| number(10,0)| Foreign Key [bookings(BookingId)]| Booking Id| | Concession| varchar2(1)| DEFAULT ‘Y’CHECK(Concession IN ‘Y’,’N’)| Concession| members| MemberId| number(5,0)| Primary Key| Member Id| | Monday| varchar2(1)| NOT NULLCHECK(Monday IN ‘Y’,’N’)| Monday| | Tuesday| varchar2(1)| NOT NULLCHECK(Tuesday IN ‘Y’,’N’)| Tuesday| | Wednesday| varchar2(1)| NOT NULLCHECK(Wednesday IN ‘Y’,’N’)| Wednesday| | Thursday| varchar2(1)| NOT NULLCHECK(Thursday IN ‘Y’,’N’)| Thursday| | Friday| varchar2(1)| NOT NULLCHECK(Friday IN ‘Y’,’N’)| Friday| | SaturdayMatinee| varchar2(1)| NOT NULLCHECK(SaturdayMatinee IN ‘Y’,’N’)| Saturday Matinee| | SaturdayEvening| varchar2(1)| NOT NULLCHECK(SaturdayEvening IN ‘Y’,’N’)| Saturday Evening| | Sunday| varchar2(1)| NOT NULLCHECK(Sunday IN ‘Y’,’N’)| Sunday| | CustomerId| number(10,0)| NOT NULLUNIQUEForeign Key [customers(CustomerId)]| Customer Id| duties| DutyName| varchar2(20)| Primary Key| Duty Title| | Description| varchar2(225)| | Duty Descript. | members_duties| MemberId| number(5,0)| Primary KeyForeign Key [members(MemberId)]| Member Id| | DutyName| varchar2(20)| Primary KeyForeign Key [duties(DutyName)]| Duty Title| performances_duties| Id| number(5,0)| Primary Key| Table Id| | PerformanceDate| date| Foreign Key [performances(PerformanceDate)]| Perform. Date| | PerformanceTime| varchar2(5)| Foreign Key [performances(PerformanceTime)]| Perform. Time| | DutyName| varchar2(20)| Foreign Key [duties(DutyName)]| Duty Title| | StaffId| number(5,0)| Foreign Key [staff(StaffId)]| Staff Id| | MemberId| number(5,0)| Foreign Key [members_duties(MemberId)]| Member Id| Discussion

The Relational Data Diagram was created based on the requirements and assumptions discussed on the Conceptual Data Model section and illustrated through the Class Diagram. The classes were converted into tables and its names pluralised in order to best describe them. The class Customer, for example, will have the list of clients stored in a table called customers. Furthermore, the identified attributes were kept in the relational database and the primary key (PK) chosen within these elements. In some cases, the PK became a combination of two attributes, such as PerformanceTime and PerformanceDate in the performances table. We have considered that these two columns could identify a unique performance, as two events could not happen at the same time.

On the other hand, in some tables, the PK could not be defined using the existing elements and the need of new attributes were inevitably. When converting the class Staff into a table, for example, we thought the StaffName could be used as the PK. However, even though unlikely, we have considered the possibility of having two staff with the same name, and therefore the StaffName could not be used as a PK. Instead, we have created a new column called StaffId. The same idea was used for the table customers, when adding the CustomerId for that table. Also, the element BookingId was added to the bookings table and MemberId to the members one, as none of the existent attributes could be used as a unique identifier.

Regarding the association classes, when converted into tables, the PK became the combination of the related tables’ PKs, as showed in the Relational Model Diagram. However, we could not use the same idea for the performances_duties table. The PK, in this case, should be the combination of PerformanceDate, PerformanceTime, DutyName and StaffId OR MemberId, depending on who would be allocated to do that duty. Therefore, StaffId or MemberId will always be null, and PKs do not allow null values. For this reason we have decided to add a new attribute (Id), which will be the PK for that table. It is also important to highlight our choice in removing one element from the seats table.

The LevelId became a foreign key (FK) from the table prices_level, which we have decided to use as part of a composed PK (SeatNumber, SeatRow and LevelId), replacing the block element showed in the Class Diagram. Another important decision taken was regarding the table members, which has the CustomerId as a FK from the customers table. In the Conceptual Data Model we have discussed the relation between Customer and Member classes, where we have stated that a Member must be a Customer. However, we must to ensure that the same member is not related to more than one customer, keeping the one-to-one relationship, as illustrated on the Class Diagram. In order to guarantee the desired relationship we have included the constraint UNIQUE for the CustomerId, avoiding the customer nformation to be used by more than one member. Implementation Table Listing staffCREATE TABLEstaff ( StaffIdnumber(5,0)PRIMARY KEY, StaffNamevarchar2(20)NOT NULL, Addressvarchar2(40), Telephonevarchar2(10), Emailvarchar2(30) ) productionsCREATE TABLEproductions ( ProductionNamevarchar2(30)PRIMARY KEY, Typevarchar2(20)CHECK (Type IN(‘Classical Music’,’Drama’,’Jazz’,’Ballet’,’Comedy’)), Descriptionvarchar2(350) ) prices_levelCREATE TABLEprices_level ( LevelIdvarchar2(1)PRIMARY KEY, Descriptionvarchar2(125) ) seatsCREATE TABLEseats ( SeatNumbernumber(2,0), SeatRowvarchar2(1), LevelIdvarchar2(1) REFERENCES prices_level(LevelId), PRIMARY KEY(SeatNumber,SeatRow,LevelId) ) roductions_pricesCREATE TABLEproductions_prices( ProductioNamevarchar2(30)REFERENCES productions(ProductionName), LevelIdvarchar2(1)REFERENCES prices_level(LevelId), Pricenumber(4,2)NOT NULL, PRIMARY KEY(ProductioName,LevelId) ) performancesCREATE TABLEperformances( PerformanceDatedate, PerformanceTimevarchar2(5), ProductionNamevarchar2(30)NOT NULL, PRIMARY KEY(PerformanceDate,PerformanceTime), FOREIGN KEY (ProductionName) REFERENCES productions (ProductionName) ) customersCREATE TABLEcustomers( CustomerIdnumber(10,0) PRIMARY KEY, CustomerNamevarchar2(20)NOT NULL, Addressvarchar2(40), Telephonevarchar2(10), Emailvarchar2(30) ) cardsCREATE TABLEcards(

CardNonumber(20,0)PRIMARY KEY, Typevarchar2(16)CHECK (Type IN (‘Visa’,’Maestro’,’Mastercard’,’American Express’)), StartDatedate, ExpireDatedateNOT NULL, CVCnumber(3)NOT NULL, Addressvarchar2(40), CustomerIdnumber(10,0)NOT NULL, FOREIGN KEY (CustomerId) REFERENCES customers (CustomerId) ) bookingsCREATE TABLEbookings( BookingIdnumber(10,0)PRIMARY KEY, Typevarchar2(9)CHECK (Type IN (‘Telephone’,’In Person’,’By Post’)), PaymentTypevarchar2(11)CHECK (PaymentType IN (‘Credit Card’,’Debit Card’,’Cash’,’Cheque’)), CardNonumber(20,0)REFERENCES cards (CardNo), CustomerIdnumber(10,0)NOT NULL, FOREIGN KEY (CustomerId) REFERENCES customers (CustomerId) ) icketsCREATE TABLEtickets( PerformanceDatedate, PerformanceTimevarchar2(5), LevelIdvarchar2(1), SeatRowvarchar2(1), SeatNumbernumber(2,0), Concessionvarchar2(1)DEFAULT ‘Y’, CHECK (Concession IN (‘Y’,’N’)), BookingIdnumber(10,0), PRIMARY KEY(PerformanceDate,PerformanceTime,LevelId,SeatRow,SeatNumber), FOREIGN KEY (PerformanceDate,PerformanceTime) REFERENCES performances (PerformanceDate,PerformanceTime), FOREIGN KEY (SeatNumber,SeatRow,LevelId) REFERENCES seats (SeatNumber,SeatRow,LevelId), FOREIGN KEY (BookingId) REFERENCES bookings (BookingId) ) membersCREATE TABLEmembers( MemberIdnumber(5,0)PRIMARY KEY, Mondayvarchar2(1)NOT NULL, CHECK(Monday IN (‘Y’,’N’)),

Tuesdayvarchar2(1)NOT NULL, CHECK(Tuesday IN (‘Y’,’N’)), Wednesdayvarchar2(1)NOT NULL, CHECK(Wednesday IN (‘Y’,’N’)), Thursdayvarchar2(1)NOT NULL, CHECK(Thursday IN (‘Y’,’N’)), Fridayvarchar2(1)NOT NULL, CHECK(Friday IN (‘Y’,’N’)), SaturdayMatineevarchar2(1)NOT NULL, CHECK(SaturdayMatinee IN (‘Y’,’N’)), SaturdayEveningvarchar2(1)NOT NULL, CHECK(SaturdayEvening IN (‘Y’,’N’)), CustomerIdnumber(10,0)NOT NULL, FOREIGN KEY (CustomerId) REFERENCES customers (CustomerId), UNIQUE(CustomerId) ) dutiesCREATE TABLEduties( DutyNamevarchar2(20)PRIMARY KEY, Descriptionvarchar2(225) ) members_dutiesCREATE TABLEmembers_duties( MemberIdnumber(5,0)REFERENCESmembers (MemberId),

DutyNamevarchar2(20)REFERENCESduties (DutyName), PRIMARY KEY(MemberId,DutyName) ) performances_dutiesCREATE TABLEperformances_duties( Idnumber(5,0)PRIMARY KEY, PerformanceTimevarchar2(5), PerformanceDatedate, FOREIGN KEY(PerformanceDate,PerformanceTime) REFERENCES performances (PerformanceDate,PerformanceTime), DutyNamevarchar2(20)REFERENCESduties (DutyName), StaffIdnumber(5,0)REFERENCESstaff (StaffId), MemberIdnumber(5,0)REFERENCESmembers (MemberId) ) Discussion Converting the Relational Model into an Oracle database was not a difficult process. Actually, we have achieved a final diagram that allowed us to easily identify tables, attributes and its associations.

The CREATE TABLE statements were created based on our knowledge acquired in the Database Systems (CI2240) module, as well as the SQL Plus tutorial available on the StydySpace. The same knowledge, as well as further discussion with teachers during the workshops, was used to choose the data types, as illustrated previously in the Data Dictionary. VARCHAR2 was used for strings, and its length defined based on the needs of each column. Names, for example, are not very long and we have assumed that 20 characters were enough. On the other hand, the production’s description could not be described with a few characters, and based on the 2012 season productions (figure 2 in the assignment description) we have defined 350 characters for this attribute.

The same idea was used to define the size of all the remaining text fields. The production’s prices will be stored in a data type NUMBER, allowing four digits for the integer part and two for the decimal one (4,2). We believe that four integer digits are more than enough, as a price would not be greater than ? 9999. 99. Also, we have limited the digits, on the right of the decimal point, as two, according to the currency precision used for prices. Different from MySQL, Oracle does not have the data type INT to be used for integers, such as IDs, Card Number and Seat Number. In order to create these integers we have also used the NUMBER, defining zero for the decimal part.

Seat Number for example, has the largest possible number equals to ‘60’, so the NUMBER(2,0) has been chosen for this attribute. Furthermore, Oracle does not have the BOOLEAN data type, as required for the attribute concession in the tickets table, as well as the entire members’ availability in the members table. In order to achieve this false/true idea, we have defined the CHECK constraint with only two options (‘Y’,’N’). In this way, the attribute will only accept the input ‘Y’, that means yes/true, and the input ‘N’ for no/false. The CHECK was also used to limit the allowed values for an attribute, such as the performance type in the performances table: ‘Classical Music’, ‘Drama’, ‘Jazz’, ‘Ballet’, and ‘Comedy’.

It is important when retrieving information from the database, such as the list of all performances where the type is equal to ‘Comedy’. If the CHECK was not declared when creating the table, the user could enter a misspelling ‘Commedy’ for one performance. Consequently this specific performance would not appear in the requested list. Finally, it is important to highlight that tables must be created in a logical order. We realised that creating a table that has a foreign key would not work, unless the related table had been already created. We have faced this problem trying to create the table seats before the prices_level one. However, Oracle provides an instant feedback with an explained error message, which was very helpful during the database implementation. Conclusion

I believe we have achieved a comprehensive system that fulfils the requirements specified in the coursework description. The decisions and assumptions taken, during the different stages of development, were clearly discussed in this report. This assignment was an opportunity, not only to apply what we have learned so far, but also to go further and improve our knowledge. We have explored the use of CASE, for example, to count the number of tickets sold separating the full price from the concession. … sum(case when tickets. levelid=’a’ AND tickets. concession=’N’ then 1 else 0 end) ” Full Price”, sum(case when tickets. levelid=’a’ AND tickets. concession=’Y’ then 1 else 0 end) “Concession. “, …

The same idea was used to show the sales breakdown for a specify performance, according to full price and concession, in each price level. It also shows the total amount and the number of tickets available in each level. Performance Details Tickets Sold as Concession on level B Tickets Sold as Full Price on level C Total Revenue from Level C Tickets Available on Level D Total Revenue from all Levels Ticket Price for Level D + All the queries are available in the Appendix A, showing the seven SELECT statements created to retrieve the required information from the relational database. Furthermore, the Appendix B shows some of the created constraints “in action”, demonstrating that the restrictions, implemented in the CREATE TABLE statements, are working properly.

Cite this Advanced Database Systems

Advanced Database Systems. (2016, Dec 12). Retrieved from https://graduateway.com/advanced-database-systems/

Show less
  • Use multiple resourses when assembling your essay
  • Get help form professional writers when not sure you can do it yourself
  • Use Plagiarism Checker to double check your essay
  • Do not copy and paste free to download essays
Get plagiarism free essay

Search for essay samples now

Haven't found the Essay You Want?

Get my paper now

For Only $13.90/page