The Concert Office Database Server Management - Case Study Example

Database Server Management Oracle SQL Table of Content Database Server Management Introduction......................................................................................................................................3 Entity Model....................................................................................................................................5 Tables and the Fields with Test Data...............................................................................................7 Stages in the Software Development Life Cycle.............................................................................8 Task 2.............................................................................................................................................10 Task 3.............................................................................................................................................11 Syntax used to create Data.............................................................................................................12 References......................................................................................................................................14 Introduction The project is the practical steps that lead to the design and implementation of an oracle database for a company called The Concert Office. The company organises a number of high-end concerts around the various cities in the UK. To do this you will need to create tables to support the following scenario. The Concert Office has scheduled concerts that may have a single principal artist performing or a group, performing for a large crowd in venues within the UK cities. In the database design, we had to analyse the data that we anticipated that the company would need to have; and we planned for how its collection and storage in the database takes place. The data anticipation in linked with a corrected determination of the data that expected from the data sources that point out at the requirements gathering stage. First, it is vital to note that these concerts bear naming scheme and their duration may only be within 2, 3, 4, or 5 hours. The concerts have to fall in three broad categories too. These categories are classical, rock, or pop and all of them have a minimum ticket cost varying from £25 and £450. These are scenarios recorded for purposes of having a consistency in data. The consistency that arose from the definitions laid bare by the anticipation of data to be collected here is what leads to the springing up of tables. Secondly, to keep the data collected in check, both in complexity and in quantity, the aspect of the concerts being scheduled and are in a repeated run makes it much easier in the database logic streamline. The events are in a variety of venues but the way that they are set up eases the schematics of the database due the recurring nature of the tables needed. The opportunities in reusing the tables and the design schemas made the work easier and the time saved was key in coming up with the best solution possible for the Concert Office database. In the analysis, stage we figured out that “REUNION” concert was one that could run in a multiple venues. This type of a scenario often anticipated a lot in the future hence the schema had to accommodate this scenario. After the design accommodating the scenario in the logic, then at least data has to be analysed to ensure maintenance in consistency. The runs that catered for here are what the team at The Concert Office term as events. The dates associated by each of the event and the venues around the UK have to be recorded. Recording is dependent on a proper datasheet and well-foreseen data collection. This scenario demands that customer’s book onto the events. The company will then need to capture the details of these customers. The details that the Concert Office will always need as they keep records of every customer include customer’s names, their gender, their telephone numbers, and street address. Still, on the requirements analysis stage, the anticipation was that the customers’ details on the time they attend an event was vital data. A capture of the attendance time seen to be much easier because most of the details about the customer are unrepeated, as it is already in the customer table. Instead, the client’s data accessed and linked to the outside table that deals with the new scenario. At an event, their time of arrival recording will take place at the venue’s entry points, and those with cars will have their car details linked with their name. Again, the data that links the client and their car had yet another potential in reducing redundancy, for it brought in a flexible design that calls up two datasets. In the table that keeps track of the cars parked at the parking lot, it is only necessary to record if the clients have parked their car in the official car park or otherwise, the registration number of the vehicle recorded. The other case in data capture is that the customers will be required to help the company by rating the concert through an evaluation each time they attend an event. The evaluation scale is any number from zero to 5.TheConcertOffice need to record the evaluation from each customer each time the customers attend an event. It is not a requirement that all events have customers registered on them, and it is not required that every customer has to have attended a concert. Each event held at an approved venue and each venue has a name, a maximum capacity (between 1,000 and 60,000 people), and a postcode. In describing this project vividly, a systematic objective report has to come in handy. A simple Entity Model The logical schemas above show the most basic relationship that is applicable in the design and the implementation o the database. The logic was done prior to the section of the database software design was selected. This design is implementable by any relational database management system. Various tables capture the data that is required by the database implementation for The Concert Office Company. The main table is the customer table. The table has fields: ID (the primary key) First name, last name, age, telephone number, street address, gender, concert ID (secondary key). These details aimed at capturing the details that are vital to the client’s records needed for record keeping in the company. The other tables that make up the database include the concert table and the cars table. The concert table aimed at capturing all the details that are applicable in keeping track of all the details and data related with the concert. The fields in this table are the concert ID, the venue, the style, and the attendance numbers of the concert. The cars table links the cars to the customer that owns the car. The various fields that are in this table are the car ID as the primary key, the customer name as the foreign key in another table and the parking number as the secondary ID. The performing artist(s) records use a different table. The table is supposed to link up with the table that keeps track of the concerts. The fields in this table include the artist ID, the type of artist, the venue, the concert ID, the attendance and the date of the concert. The tables and the fields with test data as below: Customer Table Customer ID F_Name L_Name Age Tel_No Street Address Gender Concert_ID Concert_Date 57857 Marie Yang 23 4766587 45 Hull F 6986GT 7/17/15 Concert Table Concert ID Venue Concert Style Attendance Concert_Date 6986GT Liverpool Rock 2362 7/17/15 Car Table Concert ID Parking_ID Concert Style Customer_ID Concert_Date 6986GT 767T Rock 6986GT 7/17/15 Artist Table Concert ID Artist_ID Music Style Venue Concert_Date 6986GT 4R Rock Liverpool 7/17/15 Stages in the Software Development Life Cycle Several stages are included in a Software Development Life Cycle (SDLC): The first is the Maiden Investigation stage. In this stage, the system development team will look into The Convert Office’s problem and look for ways to solve it. Strictly speaking, the team would list down the four major requirements that need factoring into the design of the system. The major ones that are indicated in the case are: to find a way to come up with a concert management and an Oracle database for data storage; a way to organize payment, collection and management of car fleets at concerts; so as to effectively manage concerts. In short, all the available information needed so that the kind of database system design will as befitting to the user as possible. The next step is the process of System Design. Here, the analysts will come up with a number of designs converted into a working model that solves that problem at hand. The most suitable design selected and the team uses that for development of the required system. Here, the team will come up with the ideal logical design of a system that implements the four requirements, either as a single entity or as a related set of interlinked system. System Coding is what follows next. Here, the team gets to the important task of translating the logical solution that they have arrived at earlier into the equivalent in terms of code. This means that the solution converted in the form that the machine will be able to understand. T After the coding stage, the team will carry out System Testing. Here, the team that implements the system will test the system that they have designed just to make sure that the system works in accordance to the needs of the client. All the flaws that the systems are hereby corrected at this stage. Then the team comes to the System Implementation stage. In this stage, the users of the system familiarize themselves with the workings of the system and they ask these users to give feedback so that the team will correct it. The Concert Office gets the system installed in their network for better running of their business process. The final stage is that which relates to System Maintenance. This refers to the final stage and it lasts as long as the system used. This is the stage that lets the team know what to upgrade, change in the system. The most widely used system life cycle models are the Systems Development Life Cycle and the Iterative and Incremental model. The SDLC model is sequential in nature and deals with a single system per cycle whereas the iterative model always optimized for large-scale software development. The progress from one stage to another is measured by the completion of one set of related tasks but in the iterative development, the incremental build is what gets iterated at every stage. The final stage of iteration is what indicates the completion of the development process. The suitable system development life cycle in this scenario is the iterative and incremental development. Its suitability is the fact that it combines iteration and increment techniques. In the case as the one of The Concert Office, four systems are required. There is a good indication that they have to be designed jointly because they are related and they are to be run on the same infrastructure. Task 2 Functional requirements refer to all the things that the program is supposed to do. They are several reasons why the functional requirements of a software project are important. These may include: The SSC system needs to be tested thoroughly so as to have surety that the system does react to the inputs and is consistent in all kinds of situations. The importance of this is that the designers are able to guarantee the company that the system that they have designed for them is what ought to be. By gathering accurate functional requirements, the design team is able anticipate the functionality of the system with these functionality. Task 3 In the case of The Concert Office, an object-oriented analysis is a technical approach that would work very well for this type of development of the system. There are four components (systems) designed for a conclusive solution. The system places each of the four systems as an object on its own. The objects are then organized and a description of how the systems (objects – in this case), interact with each other. The behaviour of the objects is the defined in each of the four components made up of very many other smaller components. The greatest aspect in this way of designing a system is that the various objects get reused and the systems development team is able to save some very valuable time in the process. The traditional methodology of systems development is based on the concept of data flow and it well portrayed by how entity relationships laid down. The basis for this in terms of systems based on the following major benchmarks in systems development: The analysis process – This is something that cuts across the conception of the conceptual model and gathering of system requirements. The system design benchmark – in implementing the system design, detailing the design and preparing it for optimal definition in terms of codes. System implementation – This is where the team should carry out the coding and then comes the testing stage. The Syntax used for data creation is as follows: CREATE DATABASE TheConcertOffice CONTROLFILE REUSE LOGFILE GROUP 1 ( `d:\database\log1a.dbf, `e:\database\log1b.dbf) SIZE 100K, GROUP 2 ( `d:\database\log2a.dbf, `e:\database\log2b.dbf) SIZE 100K DATAFILE `d:\database\data1.dbf SIZE 10M, `d:\database\data2.dbf SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 50M; Using the CREATE DATABASE command in Oracle is always a quite complex, however part of it is in indicated below. Not all the optional parameters are in use here. The more detailed code for creating The Concert Office database is shown here: CREATE DATABASE [[TheConcertOffice] [CONTROLFILE REUSE]] LOGFILE [GROUP 6] logfile [, [GROUP 6] logfile] ... [MAXLOGFILES 14] [MAXLOGMEMBERS 2] [MAXLOGHISTORY 45] [MAXDATAFILES67] [MAXINSTANCES 24] [ARCHIVELOG or NOARCHIVELOG] [EXCLUSIVE] [CHARACTER SET charset] [NATIONAL CHARACTER SET charset] DATAFILE file_specification [AUTOEXTEND OFF | ON " WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0">;[NEXT number K | M] [MAXSIZE UNLIMITED | number K | M] [, DATAFILE file_specification [AUTOEXTEND OFF | ON " WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0">;[NEXT number K | M] [MAXSIZE UNLIMITED | number K | M]] The syntax used in finding details of all concerts being held in Edinburgh SQL>SELECT class, count FROM ConcertTable WHERE class Edinburgh References Berenson, H., Bernstein, P., Gray, J., Melton, J., ONeil, E., & ONeil, P. (2012, June). A critique of ANSI SQL isolation levels. In ACM SIGMOD Record (Vol. 24, No. 2, pp. 1-10). ACM. Date, C. J., & Darwen, H. (2010). A Guide to the SQL Standard (Vol. 3). New York: Addison-Wesley. Hacigümüş, H., Iyer, B., Li, C., & Mehrotra, S. (2012, June). Executing SQL over encrypted data in the database-service-provider model. In Proceedings of the 2002 ACM SIGMOD international conference on Management of data (pp. 216-227). ACM. McFadden, F. R., Prescott, M. B., & Hoffer, J. A. (2011). Modern database management. Addison-Wesley Longman Publishing Co., Inc.. Tsirogiannis, D., Harizopoulos, S., & Shah, M. A. (2010, June). Analyzing the energy efficiency of a database server. In Proceedings of the 2010 ACM SIGMOD International Conference on Management of data (pp. 231-242). ACM. Read More