Database Management
Planning and
Conceptualization Construction
Testing Functionality Conclusion
Table of Content
1.
2.
3.
4.
Relationship (ER) Diagram, a relational schema for the
database (which comes with the ER Diagram), and a number of specified SQL queries were required on the finished database. Thinking about
different organizations I would be interested in creating a database for, the obvious options like healthcare,
education, or finance came to mind. However, I wanted to create a database not only for something I was passionate
about, but something in which I already had expertise. This line of thought resulted in the idea to create a database for a theoretical theatre company. I knew this would enable me to meet the requirements and
present a unique approach, so I got to work.
Planning and
Conceptualization
The first step in creating this database was to instantiate entities and their attributes.
Data entities are objects used in a data model meant to break In my time working on various
computer science projects, I have developed my strongest interest in database management and
reporting. While I also value web design and software programming, this is by far the field I find myself coming back to the most.
In the second semester of my junior year at American
University, I took a course called Database Management
Systems. The main objectives of this course included building an understanding of the process and parameters involved in the design and implementation of database systems. We learned about
different data models, different database designs, data
manipulation, database security, integrity, and so much more.
The final project assignment in
“Actor” represents actors in the company,
“Staff” represents anyone working the tech aspect of productions (i.e. stage manager, crew, lightboard operator, etc.),
example, a medical database might have an entity called “Patient”
with attributes like
“first_name”, “last_name”,
“date_of_birth”, and/or
“reason_for_visit” to name a few.
I was required to use these concepts to assist in the creation of an entity-
relationship model. The database needed to have several entities with relation to at least one other entity so as to stay interconnected. Relationships come in four forms: one-to-one, one-to-many, and many-to-many.
These relationships are
determined by the quantity of an entity associated with another in said relationship. For example, a health system might have a
“Hospital” entity that has a one- to-many relationship with a
“Patient” entity because there are multiple patients in one hospital.
For my theatre company’s database, I defined eight different entities: “Actor”,
“Staff”, “Cast”, “Theater”,
“Season”, “Creative_team”,
“Audience_member”, and “Ticket”.
“Theater” represents the physical locations at which productions are performed,
“Season” is the list of
productions planned for this company’s current season,
“Creative_team” represents the people involved in
creating the artistic aspects of the show (director,
lighting designer, set designer, etc.),
“Audience_member” represents the people in the audience of the productions planned, and
“Ticket” represents the
tickets reserved by audience members.
Ticket(ticket_number, seat_number, price,
seat_section, theater_name, play_title)
Audience_member(ticket_number , confirmation_number,
first_name, last_name) Theater(name, street, max_seats)
Season(play_title, playwright_name,
opening_date, closing_date, theater_name)
These entities can hold all the information necessary for a theatre company to function effectively.
The attributes to each of these entities are outlined below (format is an entity (attribute list)):
Construction
For the construction of the final database, students were required to use MySQL, an open-source relational database management system based on the language SQL (structured query language). SQL is a language used specifically for adding, removing, modifying, and querying information from a database. Most standard SQL
commands can be used with MySQL.
In order to build the entities, I had to create a set of empty
tables within SQL that I could fill with sample information.
These tables use entity attributes as columns for
organizing data instances. Below is an example of SQL code used to create the table for the “Ticket”
entity:
create table if not exists Ticket (ticket_number varchar(12) not null primary key,
seat_number varchar(5), price integer(3),
seat_section varchar(50), theater_name varchar(50) not null,play_title varchar(50) not null);
denoting the maximum amount of characters that an entry can have. For example, the
theater_name attribute is of type varchar (a string of characters) that can store a string with a maximum of 50 characters. The example below shows SQL code used to fill the
“Ticket” table with created sample data:
insert into Ticket
values("T65532926857", "G107", 102, "Orchestra", "Kreeger Theater", "Harvey");
insert into Ticket
values("T60596108598", "H101", 179, "Orchestra", "Eisenhower Theater", "Guys and Dolls");
insert into Ticket
values("T12194953219", "R110", 92, "Orchestra Grand Tier",
"Sidney Harman Hall", "Cat on a Hot Tin Roof");
insert into Ticket
values("T53466048953", "A13", 109, "Balcony", "Eisenhower Theater", "Guys and Dolls");
insert into Ticket
values("T52583813549", "M20", 179, "Orchestra", "Eisenhower Theater", "Guys and Dolls");
insert into Ticket
Actor(name, salary_per_hour, equity_status)
Staff(ID, first_name, last_name, job_title,
salary_per_hour, play_title) Creative_team(name, job_title, salary_per_hour, play_title) Cast(role, actor_name,
play_title)
To note, some attributes are
underlined, indicating that those attributes are primary keys.
Primary keys are attributes that are meant to uniquely identify an instance of data within the said entity. This means that no two instances can have the same
primary key, otherwise, they are not considered as two separate instances. For example, two
tickets cannot have the same ticket number assigned to them because the theatre company needs to count up the total number of tickets, hence the number is a primary key.
To clarify the relationship model I was working on, I was required to create an ER
Diagram. An ER Diagram is a visual representation of data entities, the relationships between said entities, as well as the types of said
relationships. Below is the ER Diagram I designed, as well as a legend that translates the visual interpretations of the relationships between entities:
This graph was created using a program called Visual
Paradigm, a tool used for
standard modeling in Computer- Aided Software Engineering (CASE). This was a required program that students
learned earlier in the course of the class. After the planning and conceptualization stage of the database was finished, it was time to start actually coding and constructing the database.
"Hand to God");
insert into Ticket
values("T42925458325", "AA12", 102, "Balcony", "Kreeger
Theater", "Harvey");
insert into Ticket
values("T11878498645", "D121", 92, "Orchestra Front", "Sidney Harman Hall", "Cat on a Hot Tin Roof");
select * from Ticket;
The above example demonstrates the INSERT INTO command in SQL that inserts an instance of data into a table. The VALUES keyword allows for a list of values to be inserted into the table together.
Each statement in the example inserts a new entry into the
table that follows the attributes set forth in the first example.
Every table was filled out using this exact methodology. The last line uses the SELECT command, which is mainly used for querying data, and the “*”, like in most other programming languages, is used to denote “all” in SQL. This line is basically telling MySQL to print out the entire table by selecting all the data instances from it.
These outputs show that the creation and subsequent filling of the entity tables was indeed successful. After building the database, the only thing left was to test it using queries.
Testing
Functionality
In order to test the project database to make sure it could function as well as other
professional databases, the
Code: select job_title,
theater_name, salary_per_hour from Staff having
salary_per_hour < 40;
Output
This is how many large companies typically interact with large databases. The queries assigned were meant to challenge our
abilities to query very specific combinations of data from our databases, and create functions that can form new data from the existing data. Here is an example of a query of specific
information I wrote for the assignment:
Find the jobs, theater names, and salaries associated with staff members with salaries below
$40/hr.
Code: create table if not exists Staff_sal_avg
(theater_name varchar(50), avg_salary integer(4));
insert into Staff_sal_avg values("Eisenhower Theater", 0); insert into
Staff_sal_avg
values("Kreeger Theater", 0); insert into
Staff_sal_avg values("Studio Theater", 0); insert into Staff_sal_avg values("Sidney Harman Hall", 0); delimiter
$$ create Trigger
salary_Averager after insert on Staff for each row begin The next example is a query I wrote that forms a new table based on existing data:
Create a trigger that keeps track of the average staff
member salary after insert into Staff.
update Staff_sal_avg
set avg_salary = (select
avg(salary_per_hour) from Staff where Staff.theater_name = new.theater_name)
where theater_name = new.theater_name;
end$$
delimiter;
select * from Staff_sal_avg;
In this example, the requirement was to write a SELECT query using the HAVING keyword. This keyword denotes that a specific condition must be met by the data in order to be queried. In this case, I queried all instances of
job_title, theater_name, and salary_per_hour from the “Staff”
entity table. To do this, I wrote a select statement with a HAVING keyword specifying the condition that all entries selected would only have hourly salaries less than $40.
Conclusion
This project is memorable to me not only because it was related to the Computer Science field I want to build a career in, but also because it allowed me to bring both of my majors
together. This was one of the only computer science projects I had complete control over as far as subject matter, and that was extremely liberating. It showed me that technological innovation can be applied to any field and that it takes ambition and perseverance to really be successful.
In this example, the requirement was to simply gather statistics on the existing data, so I
decided to write a function that would keep track of the average hourly salary at each theatre being used by the theatre
company. This function takes only the “theater_name” and “salary”
attributes from the “Staff”
entity and fills a new table called “Staff_sal_avg” with the theatre name and the average
salary per theatre. This function makes use of the UPDATE SET
command, which updates a
specified existing data instance by setting a specified value to a new value. It also makes use of the AVG() function in order to calculate the average salaries.
All queries written ended up successful and effective in fulfilling the project
requirements. This was the end of the parts of the project that I was most interested in. I ended
