MArme ALane Phase5

RECORD COMPANY
DATABASE
CS342 Database Project – Fall 2015
Mark Armendariz and Andrew Lane

Table of Contents
Phase 1: Fact-Finding, Information Gathering, and Conceptual Database Design....................................... 4
1.1 Fact-Finding Techniques and Information Gathering ................................................................. 4
1.1.1 Introduction to Enterprise ..................................................................................................... 4
1.1.2 Description of Fact-Finding Techniques ................................................................................. 4
1.1.3 Database Design Focus .......................................................................................................... 6
1.1.4 Entity and Relationship Set Description ................................................................................. 6
1.2 Conceptual Database Design .................................................................................................. 8
1.2.1 Entity Set Description ............................................................................................................ 8
1.2.2 Relationship Set Description................................................................................................ 20
1.2.3 Related Entity Set ................................................................................................................ 22
1.2.4 E-R Diagram ......................................................................................................................... 23
Phase 2: From E-R Model to Relational Model ..................................................................................... 24
2.1 Conceptual Database and Logical Database ............................................................................ 24
2.1.1 E-R Model and Relational Model ......................................................................................... 24
2.1.2 Comparison of Two Different Models.................................................................................. 25
2.2 Conversion from E-R Model to a Relational Database .............................................................. 26
2.2.1 Converting Entity Types to Relations ................................................................................... 26
2.2.2 Converting Relationship Types to Relations......................................................................... 28
2.2.3 Database Constraints ........................................................................................................... 31
2.3 Convert Record Company E-R Model to a Relational Database ................................................. 35
2.3.1 Relation Schema .................................................................................................................. 35
2.3.2 Sample Data of Relation ...................................................................................................... 42
2.4 Sample Queries for Database ................................................................................................ 52
2.4.1 Design of Queries................................................................................................................. 53
2.4.2 Relational Algebra Expressions ............................................................................................ 54
2.4.3 Tuple Relational Calculus Expressions ................................................................................. 56
2.3.3 Domain Relational Calculus Expressions .............................................................................. 57
Phase 3: Implementation of Relational Database ................................................................................. 60
3.1 Relation Normalization ......................................................................................................... 60
3.1.1 Anomalies ............................................................................................................................ 60
3.1.2 Normalization ...................................................................................................................... 61
1
3.1.3 Relation Normalization ........................................................................................................ 63
3.2 SQL *Plus ............................................................................................................................ 64
3.2.1 Main Purpose....................................................................................................................... 64
3.2.2 Oracle Schema Objects ........................................................................................................ 64
3.3 Relation Schema and Data .................................................................................................... 67
3.3.1 Contract ............................................................................................................................... 67
3.3.2 Artist .................................................................................................................................... 67
3.3.3 Composed_Of ...................................................................................................................... 68
3.3.4 Member ............................................................................................................................... 69
3.3.5 Album .................................................................................................................................. 70
3.3.6 Sold_Through....................................................................................................................... 71
3.3.7 Transaction .......................................................................................................................... 72
3.3.8 Buyer.................................................................................................................................... 72
3.3.9 Song ..................................................................................................................................... 73
3.3.10 Studio .................................................................................................................................. 75
3.3.11 Writer .................................................................................................................................. 76
3.4 SQL Queries ........................................................................................................................ 77
Phase 4: Stored Procedures, Packages, and Triggers ............................................................................ 83
4.1 Oracle PL/SQL ..................................................................................................................... 83
4.1.1 What is PL/SQL? ................................................................................................................... 83
4.1.2 PL/SQL Syntax ...................................................................................................................... 84
4.2 MS SQL Server and MySQL Stored Procedures ........................................................................ 87
4.2.1 Microsoft SQL Server and T-SQL .......................................................................................... 87
4.2.2 MySQL Server Routines........................................................................................................ 89
4.3 PL/SQL Subprogram Implementations.................................................................................... 90
4.3.1 Procedures ........................................................................................................................... 90
4.3.2 Triggers ................................................................................................................................ 94
Phase 5: Graphical User Interface ...................................................................................................... 97
5.1 User Groups ........................................................................................................................ 97
5.1.1 Executive Assistants ............................................................................................................. 97
5.1.2 Artists................................................................................................................................... 98
5.2 GUI Design and Development in Java ..................................................................................... 99
5.3 Major Features ................................................................................................................... 101
2
5.3.1 Connecting to the Oracle Database ................................................................................... 101
5.3.2 Inserting into Database ...................................................................................................... 102
5.3.3 Row Filtering For Searching ............................................................................................... 103
5.3.4 Assigning Studios to Albums .............................................................................................. 104
5.3.5 Assigning Writers to Songs ................................................................................................ 106
5.3.6 Generated Reports ............................................................................................................ 107
5.4 Outcome ........................................................................................................................... 109
3
Phase 1: Fact-Finding, Information Gathering, and
Conceptual Database Design
1.1 Fact-Finding Techniques and Information Gathering
1.1.1 Introduction to Enterprise
The imaginary company our database will be created for is for a record company called
Armenlane Records. Record companies, also known as “Record Labels”, are companies that hire
musicians and artists by contract to record albums. These contracts have certain parameters,
such as having a contract that is for a specific number of albums. After the albums are recorded,
Armenlane Records releases the albums by selling them to major retailer distribution centers,
such as Wal-Mart, Target, or Kmart. The main type of music our company focuses on is rock
music and other similar types, or genres, of music. This company rents out a small number of
independently-owned recording studios for each album to be recorded at. Each song also has a
songwriter.
1.1.2 Description of Fact-Finding Techniques
For fact-finding, we used the internet and popular search websites in order for us to find
and use information for our project. To find more specific information on record companies, we
researched a few modern record companies, as well as legal information on contracts and the
music business as a whole.
We were able to get into contact with Asthmatic Kitty Records, which is a small
independent record company based in the United States. We emailed them for information on
4
data that is stored for their company. They weren’t that specific in the information they
provided, but they gave us the general idea of what data they store in their databases, such as
artist contract information, as well as album information.
We were also able to get into contact with a musician who has recorded music at actual
studios and he was able to give us more information on what information is stored, such as
information on songs and writers.
From all of our research, we discovered that the music business is a very complex
business. Every company’s database may be different but they are probably very complex due to
how complex the music business is, so for the purpose of this project, we will keep things simple.
Contracts can be very complicated, especially when it comes to financial information and
income from music sales. But upon researching, we found that contracts often involve terms
where an artist will have only a certain number of albums they can record under their current
contract. We also discovered that record companies don’t actually own recording studios
anymore, but rent independent ones for use.
We also used the basic, and common structure of artists creating albums, which all have
at least one song, with each song having a writer. Entities were easy to discover once we laid out
what a record company does and what each artist does.
The reports that would need to be generated are numerous, but we came to identify a
few important ones. For example, general artist and album information reports for all artists
hired by the company would need to be generated, as well as financial reports and studio usage
reports would be generated. To generate a report, the user will specify what items they would
5
like to see. For example, they could see all of the songs from a specific album, or view all the
album transactions that occurred during a specific period of time.
1.1.3 Database Design Focus
Record companies can be very complicated, especially when dealing with financial
information. So for this software, we decided to focus more on music part of the record
company. Rather than covering the entire business, we will only focus on the following: The
artist and their contract information, the album and their songs, studios and songwriters, as well
as transactions for selling albums.
Keeping the main focus on artists and their music allows us to keep the project simple.
For example, if we wanted to view information for an artist who is hired by contract by the
company, we would include information based only on their role in music-making. But by
including some financial information, we are able to make a more complete database.
1.1.4 Entity and Relationship Set Description
Our time in lecture helped us develop a more solid approach to how an entity should be
created. During the lab period, we watched a few groups present their ideas for their proposed
database. During each presentation, we discussed how each idea could possibly be improved, or
if there was in issue with the current model. Using this experience, we tried to improve our own
ideas and rough drafts of our entities.
So using the information we discovered from research, we decided to focus on the
following entities: Contracts, Artists, Members, Albums, Songs, Studios, Writers, Transactions,
and Buyers.
6
The Contract entity represent the conditions by which an Artist is hired by the record
company. The Artist entity represents the group or individual that is hired by the record
company under a contract with a certain number of albums as their terms. Each artist will have
at least one member in the Member entity.
For example, if a band, called The Beatles, is hired by the company, and the band has four
members, John, Paul, George, and Ringo, the Artist entity would contain an entry for The
Beatles, but the Member entity would contain four entries, one for each member of The Beatles.
Another example is if a single artist, called Bob Dylan, is hired by the company, then the Artist
entity would contain a record for Bob Dylan and the Member entity would also contain just one
entry for Bob Dylan.
Each Artist will have albums that they have recorded. Each of these Albums will have a
Studio where it was recorded. Each Album will be sold to major retailer distribution centers. Each
of these transactions will kept track of in the Transaction entity. Each album will also have a set
of songs, and each song will have a writer.
All of these entities combine to create a database of hired artists without getting too
complex for this quarter’s project. It is a very general database, containing general artist and
music information as well as some financial information.
7
1.2 Conceptual Database Design
1.2.1 Entity Set Description
Contract – Strong Entity
Our Record Company has hundreds of artists hired by
contract to the record company, with each of these contracts
having their own terms and a date when the contract started and
ended. So a Contract entity was required.
Primary Key: contract_ID

Figure 1- Contract Entity
Attributes:
 contract_ID: Internal ID for organizing artist contracts
o String; no nulls; unique; single-valued and simple.
 album_term: Number of albums artist has to record for their contract
o int; nulls not allowed; not unique; single-valued and simple.
 start_date: Date artist signed contract
o date; no nulls; not unique; single-valued and simple.
 end_date: Date artist contract ended
o date; nulls allowed; not unique; single-valued and simple.
8
Artist – Strong Entity
Our Record Company has hundreds of artists hired by
contract to the record company. So we needed an Artist Entity
to represent these artists in our database.
Primary Key: artist_ID
Attributes: Figure 2- Artist Entity
 artist_ID: Internal ID for artist information

o string; no nulls; unique; single-valued and simple.
 artist_name : Name used by artist

o string with any value; no nulls, not unique; single-valued and simple.
 genre: Style of music classification
o string with any value; nulls allowed; not unique; single-valued and simple.
9
Member – Strong Entity
Artists may have more than one member as part of their group, or
they may just be a single person as the only member of the artist. For this
reason, a Member Entity is required.
Primary Key: ssn
Attributes:
 ssn: - Social Security Number unique to each person
o String with length of 9 characters; no nulls; unique.
 fname : First name

o string with any value; no nulls; not unique; single-valued Figure 3 - Member Entity
and simple.
 lname: Last name
o string with any value; no nulls; not unique.
 phone: Phone number
o string with length of 10 chars; nulls allowed; not unique; single-valued and simple.
 addr: Street address
o string of length 40 chars; nulls not allowed; not unique.
 city: Specific city in U.S. State
o string of length 40 chars; nulls not allowed; not unique.
 state: State located in the U.S.
10
o string; nulls not allowed; not unique; single-valued and simple.
 zip: Valid U.S. Zip Code
o string: nulls not allowed; not unique; single-valued and simple.
 instrument: Instrument that member plays
o string; null not allowed; not unique; single-valued and simple.
 start_date: date member’s contract began
o Date type; null not allowed; not unique; single-valued and simple.
 end_date: date member’s contract ended
o Date type that is after starting date; nulls allowed; not unique. Single-valued and
simple.
11
Album – Strong Entity
Each Artist will have a certain amount of Albums. So an
Albums entity is required in order to store general information for
each album recorded by the group.
Primary Key: album_ID
Attributes:
Figure 4 - Album Entity

 album_ID: Unique album ID number
 album_name : Name of album

o string, no nulls, not unique; single-valued and simple.
 Date_released: Date album released
o date; no nulls; no unique; single-valued and simple
 Unit_price: Price of album sold in stores
o float; nulls allowed; not unique; single-valued and simple.
12
Song - Strong Entity
Every Album will have a set of songs listed in a particular
order with a certain length. So for these reasons, a Song entity is
required in order to store each of these songs that are associated
with a particular album released by a particular artist.
Primary Key: song_ID

Figure 5 - Song Entity
Attributes:
 song_ID: - Internal ID for song organization

 track_no: Track number of song on album
o int; nulls allowed; not unique; single-valued and simple.
 song_name : Name of the song
o string; no nulls; not unique; single-valued and simple.
 song_length: Length of song in time
o time; nulls allowed; not unique; single-valued and simple.
13
Studio – Strong Entity
This recording company rents out independently-owned
studios for artists to use. In order to keep track of where each song
was recorded and how much money was spent in recording this a
particular albums or on an artist, a Studio entity is required to hold
information for each studio.
Primary Key: studio_ID
Attributes:
 studio_ID: - Internal ID for contract organization Figure 6 - Studio Entity
 studio_name: Name of studio

 phone: contact number of studio
o string; nulls allowed; not unique; single-valued and simple.
 addr: street address where studio is located
 city: city where studio is located
14
 state: Specific state where studio is located
 zip: zip code of studio
 open_date: Date studio opened
 close_date: Date studio closed
o date that takes place after open_date; nulls allowed; not unique; single-valued
and simple.
 hrly_cost: Hourly cost to rent studio for recording
o float; nulls not allowed; not unique; single-valued and simple.
15
Writer – Strong Entity
The recording company will contract out writers to either
compose lyrics or tracks for the artists to sing or use. A writer can
work on many different songs and is not limited to the amount he
can work on unless specified in the contract.
Primary Key: writer_ID
Attributes:
 writer_ID: Internal ID for contracted writer

Figure 7 - Writer Entity
 fname: Name of writer

 lname: Last name of writer
 ssn: Social security number
 addr: Street address of writer
 phone: Phone number of writer
16
 city: City where writer lives
 state: Specific state where writer lives
 zip: zip code of where writer lives
 start_date: Date writer signed contract
 end_date: Date writer contract ended
o date that takes place after start_date nulls allowed; not unique; single-valued and
simple.
17
Transaction – Strong Entity
Albums will be sold to retail distribution centers through
transactions, so a transaction entity is required to hold the date
and unique ID for each transaction.
Primary Key: transaction_ID

Figure 8- Transaction Entity
Attributes:
 transaction_ID: Internal ID for transactions

 Date: Date when batch of albums was purchased
o date; nulls allowed; not unique; single-valued and simple.
18
Buyer – Strong Entity
Information for retail distribution centers is required to be
stored for keeping transaction information. So a buyer entity is
required.
Primary Key: buyer_ID
Attributes:
 buyer_ID: Unique buyer ID

Figure 9- Buyer Entity
 buyer_name: Name of major buyer

 phone: phone number
 city: City where buyer is located
 state: Specific U.S. state
 zip: zip code
19
1.2.2 Relationship Set Description
Contract hires Artists
This relationship links each artist to the contract by which they were hired. Each artist
has one contract, and each contract has one artist, so it is a One to One relationship. This
relationship utilizes the Contract and Artist entities.
Artist is composed of Members
This relationship links each artist to the members to make up that artist. Single artists
will have only member in the member entity, and bands will have more than one member in the
member entity.
Using the same example from earlier, if the band The Beatles were hired, there would
be a single entry in the Artist entity for “The Beatles”, but there would be four entries for the
band members John, Paul, George, and Ringo, in the Member entity.
However, this relationship is a Many to Many relationship with any number of artists
having any number of members and any number of members have any number artists. So a
member can be a part of more than one group or single artist. This relationship utilizes the Artist
and Member entities.
Artist records Albums
This relationship links each artist to all the albums they have recorded. It is a One to
Many relationship with one artist having any number of albums and any number of albums
20
having one artist. This relationship utilizes the Artist and Album entities.
Album is composed of Songs
This relationship links each album recorded to all of the songs that are on that album. It
is a One to Many relationship with one album have any number of songs and any number of
songs having one album. This relationship utilizes the Album and Song entities.
Albums sold through Transactions
This relationship links each album to the transactions the recording company makes in
order to sell an album to a major retailer. It is a Many to Many relationship with any number of
albums having any number of transactions, and any number of transactions having any number
of albums. This relationship utilizes the Album and Transaction entities.
Transactions purchased by Buyer
This relationship links each transaction to the buyer who purchased the batch of
albums. It is a Many to One relationship, with any number of transactions being purchased by a
single buyer, and one buyer having any number of transactions. This relationship utilizes the
Transaction and Buyer entities.
Albums recorded at Studios
This relationship links each album to the recording studio it was recorded at. It is a
Many to One relationship with any number of albums being recorded at one studio and one
21
studio having any number of albums. This relationship utilizes the Album and Studio entities.
Songs written by Writers
This relationship links each song to the writer who wrote the song. It is a Many to One
relationship with any number of songs written by one writer and one writer writing any number
of songs. This relationship utilizes the Song and Writer entities.
1.2.3 Related Entity Set
Generalization is when entities are grouped together to show a more general view. With
generalization, we bring together multiple entities into one entity to create a larger entity based
on their similar traits and characteristics.
On the other hand, specialization is the opposite of generalization. With specialization,
entities are divided into sub classes based on their traits. You can take an entity such as artist,
and split it into subclasses like Band or Solo. These are both very similar to the entity Artist.
We thought about possibly keeping Bands and Solo artists separate with two distinct
entities, but they have very similar attributes. So we decided to generalize them into a single
entity called Artist. What will eventually separate the two separate ideas is the number of
members this entity is related to in the Member entity. An artist with one entry in the member
entity will be a solo artist, and an artist with more than one entry in the member entity will be
considered a group or band.
22
1.2.4 E-R Diagram
An E-R model, or entity-relationship model, is a visualization of entities and how they are
related to other entities. This includes the relationships between entities and their cardinalities.
This model allows for entities to be organized in meaningful way.
Referring back to all previous information and development, we created the following E-R
Model for this database. It is a very basic and high level understanding of what our company
would require for this database. This is tentative, as this design may change as we progress with
this project. But, we can now use this model to create a relational database.
Figure 10- E-R Model of Record Company Database
23
Phase 2: From E-R Model to Relational Model
2.1 Conceptual Database and Logical Database
2.1.1 E-R Model and Relational Model
E-R Model
The Entity-Relationship model was created by Peter Chen, a Computer Science and
Applied Mathematics graduate from Harvard. Chen created the model to create a formal
approach on data modeling in order to develop a database.
The E-R model has a few major features that assist in the development and visualization
process of creating a database. E-R modeling allows the developer to create a graphical
representation of how their database will look. Creating Entities which hold the place of real-life
conceptual or physical objects with independent existence, a data model can be created by
adding attributes to the Entity in order to fully describe it, and then by linking Entities together
through relationships with real-world meaning. For example, if entities EMPLOYEE and PROJECT
are created, they may be linked together by EMPLOYEE WORKS_ON PROJECT. In this way, a data
model can be created which is a vital step towards developing a database.
Relational Model
The Relational Model, created by IBM researcher Ted Codd in 1970, is a data model
rooted in set theory and first-order predicate logic that is widely used in developing modern
database systems. Organized into tables, or more formally called “relations”, the two main
methods of representation are through the tuple (row) and by attribute (column). Through the
24
use of unique primary keys, data requests, or queries, can be used to for extracting data from
the database. Through the use of what are called foreign keys, relations can be linked in a similar
fashion to the way the E-R model links entities together, allowing for a simplistic means of
storing data, and yet a powerful means of organizing data for retrieval.
2.1.2 Comparison of Two Different Models
Differences and Similarities
The E-R and Relational Model both help to visualize and conceptualize what the actual
database design will eventually be. Developers use the E-R model to visualize the main
conceptual or physical entities of their database, as well as the attributes for each entity, and
each relationship between other entities. The Relational Model is used to assist the developer to
finalize their proposed database layout, as well as to create relations from proposed entities and
link them together through foreign keys or relation tables, but the relational model could be
difficult to create without some form of visual aid. So the E-R model helps with the initial design,
but the two models are so similar that the E-R model can be easily converted to the Relational
Model for physical database development using a DBMS, such as Oracle.
Advantages and Disadvantages
An advantage of using the E-R model over the relational model is the visualization aspect.
In the E-R model, entities, attributes, and relationships can be easily visualized in a diagram using
shapes that are linked together. In the relational model, relations are represented as tables,
which aren’t as visually appealing and are harder to work with when designing a database.
25
Another advantage the E-R Model has over the Relational Model is the fact that the E-R
Model fully supports multi-valued and composite attributes while the Relational Model only
supports single-valued and simple attributes. This can be problematic when real-world objects
work better with multi-valued and composite attributes, such as when a car has more than one
color. Conversion techniques can take care of this, but it is certainly an advantage over the
Relational Model.
The main disadvantage of the E-R Model is that there does not exists a query language
for that model. The Relational Model has the advantage of the SQL language, used for retrieving
data, making the Relational Model a much more usable model.
2.2 Conversion from E-R Model to a Relational Database
2.2.1 Converting Entity Types to Relations
Strong Entity Types
When converting strong entity types to relations in the Relational Model, you must
create a Relation that contains all of the simple attributes that are a part of the entity being
converted. The relational model only supports simple attributes, and not composite or multi-
valued attributes. One of the key attributes from the entity must also be selected as the primary
key of the newly created relation.
Weak Entity Types
When converting weak entity types to relations in the Relational Model, a relation can be
created using all of the simple attributes of the weak entity being converted. But because this is
26
a weak entity, which does not have a key of its own, a foreign key must be created from the
primary key of the parent entity, so that the relations can be properly mapped for the relational
model.
Simple and Composite Attributes
Only simple attributes are supported by the Relational Model. So for simple attributes in
the E-R Model, simply include all simple attributes as simple attributes of their respective
relation.
Composite attributes are not supported by the Relational Model. So for converting E-R
Model composite attributes, you will break the composite attribute into a set of simple
attributes that can easily be used in their respective relation. You can also create a separate
relation for composite attributes instead of splitting them into simple attributes.
Single-valued and Multi-valued Attributes
Only single-valued attributes are supported by the Relational Model. So for single-valued
attributes in the E-R Model, include them as simple, single-valued attributes in their respective
relation.
Multi-valued attributes are not supported by the Relational Model. So for each multi-
valued attribute, you must create a new relation that will hold the values for that multi-valued
attribute. This new relation will have a foreign key that will relate this table to its parent relation
27
2.2.2 Converting Relationship Types to Relations
One to One Relationship
There are three options available for converting binary One to One relationship types.
Given entities T and S who are in a One to One Relationship:
1. The first option is to include a foreign key in of the relations that references the other
relation. Total participation of one entity in the other is very helpful in this case.
2. The second option is actually merge the two entities into one relation. The total
participation from both entities is required for this to work.
3. The last option for binary One to One relationships is to create a cross-reference relation.
Given entities T and S that are in a One to One relationship, create a relation from the
relationship between T and S. The primary key of this relation R will be a combination of
the primary keys of T and S. Any simple attributes from the relationship will be included
as attributes of this new relation R.
One to Many Relationship
For converting binary One to Many Relationship types there are two options available.
Given entities T and S who are in a One to Many Relationship:
1. If S is on the N-side of the relationship, include the primary key of T in the S relation as a
foreign key. All simple attributes of the relationship are included as attributes of the S
relation.
28
2. The last option for binary One to Many relationships is to create a cross-reference
relation. Given entities T and S that are in a One to Many relationship, create a relation
from the relationship between T and S. The primary key of this relation R will be a
combination of the primary keys of T and S. Any simple attributes from the relationship
will be included as attributes of this new relation R.
Many to Many Relationship
For converting binary Many to Many relationship types, there is only one option available.
Given entities T and S who are in a Many to Many relationship:
1. The last option for binary Many to Many relationships is to create a cross-reference
relation. Create a relation from the relationship between T and S. The primary key of this
relation R will be a combination of the primary keys of T and S. Any simple attributes
from the relationship will be included as attributes of this new relation R.
IS-A and HAS-A Relationships
IS A Relationships are specialization relationships. Specialization is the process of creating a
set of subclasses from a certain superclass entity type. The resulting set of subclasses that forms
a specialization is defined on the basis of some defining characteristic of the entities within the
superclass.
This is the opposite of the abstraction process which we disregard the differences among
several entity types and then identify their like features. Then, we can generalize them into a
single superclass. This results in the original entity types being special subclasses. The term
29
generalization is used to refer to the process of defining a generalized entity type from the given
entity types.
For converting an E-R specialization or generalization relationship to the Relational
Model, there are four options:
1. Create relations for all superclasses and subclasses. Include a foreign key for each subclass
relations to link them to their respective superclass. This works for any specialization.
2. Create relations only for the subclasses. This only works for specialization, and when
the subclasses have total participation in the relationship.
3. Create a single relation for all super and subclasses, but the relation will contain a
discrimination attribute. This attribute will indicate what subclass each tuple belongs to,
if it indeed participates in the relationship.
4. Create a single relation with a set of type attributes that will indicate which subclass
each tuple belongs to. This works for overlapping subclasses in specialization.
Recursive Relationship
A recursive relationship is when an entity is in a relationship with itself. To convert these
relationships to the Relational Model, we can create a foreign key that will reference the primary
key of its own relation. This way it can be in a relationship with itself.
For example, say in a company, there are Employees and Managers, but in the database
there is only an Employee relation that contains both lower-level employees and upper-
management employees. We can look at an employee tuple, and if the employee is a manager,
30
his primary key will show up as the foreign key in another employee tuple who is a lower-level
employee. So it is in a recursive relationship.
N-ary Relationships
In an n-ary relation, you have more than 2 entities participating in a relationship. So for
converting these relationships to the Relational Model, you must create a cross-reference table,
which will contain an n number of foreign keys to relate each of the relations together. You
would also include any and all simple and single-valued attributes.
Union Type Relationship
In the E-R Model, union type relationships, or categories, are when you create a subclass
from the union of two or more entity types. These subclasses are otherwise known as a union
type or a category.
For converting these relationships to the Relational Model, you must create a new
relation which will only hold a surrogate key. A surrogate key is a new unique attribute created
to uniquely identify members of the union relationship. This new key will then be added as a
foreign key to each of the participating super-classes
2.2.3 Database Constraints
Databases must obey certain constraints that are either inherent in the relational schema
itself, or are from business rules. Constraints are directly related to the database schema, or the
method of organization. Constraints are related to how the data will be handled when deleting,
inserting, or updating, and to ensure the integrity of the data in the database.
31
Domain Constraints
Domain constraints specify that all of the values within a tuple must be within the
specified domain for each attribute, such as their datatype or enumerated datatype. For
example, if the attribute’s domain is an integer, than that attribute’s value for all tuples must be
an integer. Or if the domain is an enumerated datatype, the attribute’s value for all tuples must
exist within that enumerated set. When updating or inserting, the DBMS will not allow you to
add a value to an attribute that does not exist in the domain of the attribute.
Key Constraints
Because the Relational Model is based on set theory, which by definition states that all
elements in a set must be unique, there must be a way to distinguish tuples from each other for
data retrieval. Through the use of primary and candidate keys, we can use these unique values to
identify unique records in a database. However, a relation can have multiple unique keys, or
candidate keys. One must be chosen as the primary key for this purpose. When updating or
inserting, the DBMS will not allow you to create a key that is not unique.
NULL Constraints
Another constraint is when an attribute is not allowed to be null, such as for a first name
or last name of a student. For updating or inserting, if the attribute is specified to be non-null,
the DBMS will not allow you to insert null data.
32
Entity Integrity Constraints
The entity integrity constraint is the constraint where no primary key can be null. A
primary key is used to identify unique records in a database, and each table is only allotted one
primary key, so constraining the primary key to always being unique and not NULL is important.
With joins, primary keys can also be used to relate tables together. For inserting or updating, the
DBMS will not allow you to have a null primary key.
Referential Integrity Constraints
Referential Integrity Constraint is a constraint where a foreign key in a relation must
reference an existing tuple from the relation it is referencing at any given time.
This constraint is important for database operations. For example, the delete operation
can only be done if certain requirements are met. In our database, we could not delete an artist
from the Artist relation without also deleting any and all albums and songs because this would
violate the referential integrity constraint. For inserting or updating data in the database, you
can’t make foreign keys that reference tuples in other relations that do not currently exist. This
would also violate the referential integrity constraint.
Check Constraint
Check constraints are constraints that are defined upon creating a database table using
SQL. Check constraints are used to ensure the integrity of data that is being updated or inserted
by making sure that a specific condition is met. If the value being inserted is not null, then the
33
check constraint will evaluate to TRUE or FALSE. If the value being inserted is null, then the check
constraint will evaluate to UNKNOWN, but it will not violate the constraint.
An example of a check constraint would be to say that the price of an item is greater than
$0.00. So that when data is entered or updated, no item in the database can have a price of less
than or equal to $0.00. So, when inserting or updating data, if the data evaluates the check
constraint to false, the DBMS will not allow you to insert that data.
Business Rules
Business rules are policies that businesses follow that must also apply to the data in their
databases. For example, if a company’s policy states that an accountant can’t have a relative
who works as a cashier, this rule also needs to be enforced in the database through either
triggers or the application software.
These rules essentially maintain proper relationships between entities as well as ensure
the integrity of the data. For example, for our database we have an entity called Artist. Every
Artist has Albums, but no Album can exist without an Artist. Below are several more examples
specific to our database.
1. No Member can exist in the database without being a part of at least one Artist.
2. No Artist can exist in the database without having a Contract in the database.
3. No Song can exist in the database without having an Album in the database.
4. No Album can exist in the database without having an Artist in the database.
34
5. No Transaction can exist without a Buyer, and no Transaction can exist without having at
least one Album being sold in that Transaction.
2.3 Convert Record Company E-R Model to a Relational

Database
2.3.1 Relation Schema
Using all previously mentioned conversion techniques from E-R Model to Relational
Database, we are able to convert our entities to relations. Each of our Entities in our E-R Model
we created were strong entities, so we created relations for each of them with all simple
attributes.
All of the relationships we created between entities were either 1:N, N:1, or N:M. So for
all of our 1:N or N:1 relationships, we used foreign keys for the conversion to the relational
model. For the two N:M relationships we have, we used the method of creating a relation, where
each tuple is an instance of a relationship between two relations.
The following tables are representations of our relation schemas for our relational
database design.
35
Keys PRIMARY FOREIGN
Contract
For converting the strong entity Contract, we created a relation that contains all of the
simple attributes from the Contract entity. The primary key is contract_ID.
Attribute Domain Description

Contract_ID String Unique contract ID
Number of albums under
Album_terms Int
contract
Start_date Date Date contract started
End_date Date Date contract ends
Artist
For converting the strong entity Artist, we created a relation that contains all of the
simple attributes from the Artist entity. For the Contract Hires Artist relationship, which is 1:1,
we’ve added a foreign key, contract_ID, to the Contract relation. The primary key is artist_ID.

Artist_ID String Unique artist ID
Contract_ID String Contract number
Artist_name String Name of artist
Genre String Type of music
Composed_Of
Composed_Of was a relationship between the two strong entities Artist and Member. It
is a N:N relationship, so for converting, we’ve created a relation where each tuple is an instance
of that relationship. The primary key of this relation is the combination of the primary keys from
Artist and Member.
36
artist_ID String Unique artist ID
Ssn String Social Security Number of
artist member
Member
For converting the strong entity Member, we created a relation that contains all of the
simple attributes from the Member entity. The primary key is ssn.

Ssn String Social security number
Fname String First name
Lname String Last name
Phone String Phone number
Address String Street address
City String Name of city
State String U.S. state
Zip String Zip code
instrument String Instrument played
Album
For converting the strong entity Album, we created a relation that contains all of the
simple attributes from the Album entity. For the Artist Records Album relationship, which is 1:N,
we’ve added a foreign key, artist_ID, to the Album relation. For the Album Recorded At Studio
relationship, which is N:1, we’ve added a foreign key, studio_ID, to the Album relation. The
primary key is album_ID.
37
Album_ID String Unique album ID
Album_name String Album name
Date_released Date Date album released
Unit_price Float Selling price per album
artist_ID String Unique artist ID
Studio_ID String Unique studio ID
Start_date Date Date work on album began
End_date Date Date work on album ended
Hrs_worked Int Number of hours worked on
album
Sold_Through
Sold_Through was a relationship between the two strong entities Album and Transaction.
It is a N:N relationship, so for converting, we’ve created a relation where each tuple is an
instance of that relationship. The primary key of this relation is the combination of the primary
keys from Artist and Member.

Album_ID String Unique ID for album
Transaction_ID String Unique ID for transaction
Batch_units Int Amount of each album in
transaction
Transaction
For converting the strong entity Transaction, we created a relation that contains all of the
simple attributes from the Transaction entity. For the Transaction Purchased By Buyer
relationship, which is N:1, we’ve added a foreign key, buyer_ID, to the Buyer relation. The
primary key is transaction_ID.
38
Transaction_ID String Unique transaction ID
Buyer_ID String Unique buyer ID
Date Date Date of purchase
Buyer
For converting the strong entity Buyer, we created a relation that contains all of the
simple attributes from the Buyer entity. The primary key is buyer_id.

Buyer_ID String Unique buyer ID
Buyer_name String Name of buyer
Phone String Phone number
City String City
Zip String Zip code
Song
For converting the strong entity Song, we created a relation that contains all of the
simple attributes from the Song entity. For the Album Composed Of Song relationship, which is
1:N, we’ve added a foreign key, album_ID, to the Contract relation to link songs to their album.
For the Song Written By Writer relationship, which is N:1, we’ve added a foreign key, writer_ID,
to the Contract relation to link songs to their Writer. The primary key is artist_ID.

Song_ID String Unique Song ID
Song_name String Song name
Track_no Int Track number on album
Song_length Time Length of song
Album_ID String Unique album ID
Writer_ID String Unique writer ID
39
Studio
For converting the strong entity Studio, we created a relation that contains all of the
simple attributes from the Studio entity. The primary key is studio_ID.

Studio_ID String Unique Studio ID
Studio_name String Name of studio
phone String Phone Number
Zip String Zip code
Open_date Date Date studio opened
Close_date Date Date studio closed
Hrly_cost Float Cost per hour to rent studio
Writer
For converting the strong entity Writer, we created a relation that contains all of the
simple attributes from the Writer entity. The primary key is writer_ID.

Writer_ID String Unique ID for writer
fname String First name
lname String Last name
Ssn String Social Security Number
phone String Phone Number
Zip String Zip code
40
Relational Model
Keys PRIMARY FOREIGN
41
2.3.2 Sample Data of Relation
Contract
ContractID AlbumTerms StartDate EndDate

C17813 1 4/22/2010 4/1/2011
C44086 3 5/29/2010 5/1/2013
C11328 5 4/14/2011 NULL
C4752 3 12/06/14 NULL
C6789 2 9/20/2010 9/1/2012
C55390 5 10/22/13 NULL
C85862 6 10/17/12 NULL
C34728 2 4/8/2014 NULL
C93471 4 11/22/12 NULL
C50643 2 3/8/2010 3/1/2012
C19760 1 11/11/14 NULL
C53622 1 9/4/2011 9/1/2012
C42579 2 6/18/2012 6/1/2014
C96710 4 4/5/2014 NULL
C67044 5 8/23/2010 8/1/2015
Artist
Artist_ID ContractID Artist_Name Genre

A17813 C17813 Solomon Folk
A44086 C44086 Bell Rock
A11328 C11328 Hilda Rock
A4752 C4752 Baker Metal
A6789 C6789 Jamalia Classic
A55390 C55390 Linus Folk
A85862 C85862 Steven Rock
A34728 C34728 Colette Alternative
A93471 C93471 Adena Folk
A50643 C50643 Angela Rock
A19760 C19760 Ralph SoftRock
A53622 C53622 Eliana SoftRock
A42579 C42579 Chanda Alternative
A96710 C96710 Yardley Folk
A67044 C67044 Solomon Rock
42
Composed Of
Artist_ID SSN
A17813 115-80-7312
A17813 898-63-6127
A17813 095-12-8618
A17813 718-40-2914
A17813 094-10-3269
A17813 390-79-6801
A17813 799-51-6609
A44086 466-69-1455
A44086 913-28-7463
A44086 316-63-2012
A44086 706-38-8832
A11328 230-40-5066
A11328 237-09-5808
A11328 030-52-9568
A4752 350-82-6131
A4752 966-72-8262
A4752 174-63-5988
A4752 555-69-8350
A4752 522-64-6239
A4752 033-39-0750
A4752 752-64-0200
A6789 060-96-8225
A6789 704-11-3574
A6789 380-38-4098
A6789 936-50-7878
A6789 080-69-9386
A55390 445-08-9490
A55390 694-77-5842
A55390 359-68-4742
A55390 022-62-8780
A55390 727-70-6860
A85862 297-66-5184
43
A85862 727-43-1600
A85862 835-47-1116
A34728 681-99-1076
A34728 326-33-7324
A34728 794-07-1975
A34728 065-29-1852
A93471 238-44-2093
A93471 647-75-9371
A50643 493-54-0743
A50643 315-18-5134
A19760 934-66-9708
A53622 103-18-0494
A53622 689-61-9138
A53622 519-05-5209
A53622 130-87-1020
A53622 648-37-7849
A42579 018-38-7601
A42579 647-90-6445
A42579 737-34-7478
A96710 043-98-9774
A96710 276-39-2123
A67044 800-96-0314
A67044 055-92-3612
A67044 372-27-3921
A67044 703-99-8621
A67044 815-09-6438
A67044 249-64-8859
A67044 539-63-8320
A67044 985-14-8004
A67044 355-42-2240
A67044 196-45-7896
44
45
Sold Through
TransactionID AlbumID Batch Units

T17813 AL3626 200000
T44086 AL4620 250000
T11328 AL2151 150000
T4752 AL7741 10000
T6789 AL2381 500000
T55390 AL5377 25000
T85862 AL5780 1000
T34728 AL1803 100
T93471 AL5392 55000
T50643 AL7282 230000
T19760 AL725 100000
T53622 AL8817 1500
T42579 AL9098 2000
T96710 AL3881 250
T67044 AL4688 100000
46
Transaction
TransactionID Buyer_ID Date
T17813 B00001 10/05/15
T44086 B00005 06/26/14
T11328 B00003 07/25/14
T4752 B00004 09/09/14
T6789 B00005 09/23/15
T55390 B00006 03/21/15
T85862 B00004 11/21/15
T34728 B00005 09/18/14
T93471 B00006 04/10/15
T50643 B00005 08/05/14
T19760 B00005 04/01/15
T53622 B00001 01/21/14
T42579 B00004 11/30/13
T96710 B00006 09/19/15
T67044 B00001 10/05/15

Buyer
Buyer_ID Buyer_name phone address city state zip

B00001 FYE 1-410-941-0560 7910 Dolor. Road Tacoma WA 25345
B00002 Kmart 1-379-875-2434 8617 Adipiscing Rd. West Valley City UT 81502
B00003 Wal-Mart 1-932-271-7830 6554 Urna St. Boise ID 41529
B00004 Costco 1-249-978-7321 4592 Aliquet. Road Aurora CO 49142
B00005 Target 1-184-341-5591 1136 Id Street Chicago IL 34218
B00006 AmoebaRecords 1-481-394-3965 9627 Amet St. Jonesboro AR 72604
B00007 The Warehouse 1-463-400-3980 4382 Augue St. Madison WI 37518
B00008 Suncoast 1-421-736-7118 6594 Urna Avenue Boise ID 40083
B00009 Shop Here Please 1-121-935-1648 5537 Donec Street Springfield IL 91158
B00010 Dollar General 1-460-442-9430 4400 Aliquet Av. Kailua HI 32090
B00011 One Stop Shop 1-464-348-8587 5161 Aenean Rd. Billings MT 94754
B00012 S-Mart 1-656-784-3345 8367 Habitant St. San Diego CA 92003
B00013 Quick Stop Groceries 1-618-594-7681 3545 Quisque Street Saint Paul MN 69624
B00014 Sears 1-457-567-9254 2714 Nunc, Street Davenport IA 67485
B00015 Records R Us 1-613-336-1368 8167 Nibh. Road Baltimore MD 76135
47
Song
Song_ID Track_Number Song_name Song_length AlbumID WriterID
SO8882 1 Dude Dude 8:84 AL3626 W1262

SO7345 2 Shut Clock 6:58 AL3626 W1262
SO3375 3 Unkempt Bone 9:13 AL3626 W1262
SO8701 4 Aquatic Downtown 2:27 AL3626 W1262
SO9804 5 Premium Sign 3:76 AL3626 W1262
SO0740 6 Old-Fashioned Bead 4:55 AL3626 W1262
SO8528 7 Neat Pigs 3:99 AL3626 W1262
SO4682 8 Few Crack 4:85 AL3626 W1262
SO8488 1 Flashy Ocean 4:77 AL3626 W8241
SO1533 2 Faithful Whip 0:76 AL3626 W8241
SO3099 3 Confused Screw 6:10 AL4620 W8241
SO1814 4 Holy Hell 1:98 AL4620 W8241
SO6409 1 Tent Treatment 8:53 AL4620 W8241
SO3550 2 Rainstorm Apples 6:02 AL4620 W8241
SO1839 3 Birth Soap 2:06 AL4620 W8241
SO2571 4 Minute Popcorn 5:35 AL4620 W8241
SO6969 5 Jewel Note 3:57 AL4620 W8241
SO7506 6 Change Question 6:68 AL4620 W5458
SO5478 6 Letter Circle 5:64 AL4620 W5458
SO5772 7 Base Fork 3:48 AL4620 W5458
SO1843 8 Good-Bye Meeting 1:32 AL4620 W5458
SO4135 9 Fight Expansion 6:61 AL4620 W5458
SO1915 1 Light syrup 5:21 AL4620 W5458
SO2408 1 Object Book 7:81 AL4620 W5458
SO0776 2 Marry Touch 6:10 AL4620 W5458
SO8410 3 Justify Chin 8:68 AL2151 W5894
SO8202 4 Bounce Stick 5:32 AL2151 W5894
SO2521 5 Cheat Air 5:52 AL2151 W5894
SO1231 6 Breed Uncle 1:68 AL2151 W5894
48
SO8531 7 Mislead Beggar 9:81 AL2151 W5894
SO3251 8 Analyze Secretary 6:07 AL2151 W5894
SO3942 9 Exercise View 0:25 AL2151 W5894
SO1421 10 Intend Approval 3:16 AL2151 W5894
SO9412 11 That’s too much 6:62 AL7741 W5894
SO4618 12 Talented Turkey 1:33 AL7741 W5894
SO0985 13 Arrogant Apples 5:38 AL7741 W6310
SO6835 14 Young Yard 4:93 AL7741 W6310
SO8417 15 Simple Scent 9:76 AL7741 W6310
SO9423 16 Ten Town 7:74 AL7741 W6310
SO4161 17 Comfortable Cactus 6:49 AL7741 W6310
SO3935 1 Cooing Crayon 6:69 AL7741 W6310
SO8108 2 Pricey Party 8:73 AL7741 W6310
SO0478 3 Poised Plastic 9:63 AL7741 W6310
SO5138 4 Silly Scale 9:45 AL7741 W4552
SO3275 5 Ketchup and A1 3:11 AL7741 W4552
SO2116 6 Wax War 6:14 AL7741 W4552
SO3114 7 Art Ants 8:46 AL7741 W4552
SO6665 8 Mind Minister 7:05 AL7741 W4552
SO6383 9 Mother Mice 0:63 AL7741 W4552
SO4064 1 Texture Tramp 7:55 AL7741 W4552
SO0519 2 Bread Bean 1:86 AL2381 W4552
SO6505 1 Ship Space 5:97 AL2381 W4552
SO8475 2 Prose Produce 6:98 AL2381 W4552
SO8551 1 Plough Pump 2:11 AL2381 W4552
SO1457 1 Skate Spot 1:04 AL5377 W1384
SO6451 2 Don’t touch me 4:69 AL5377 W1384
SO9401 3 Ascertain Army 2:36 AL5377 W1384
SO0623 4 Jail Join 7:62 AL5377 W1384
SO0892 5 Branch Bath 4:44 AL5377 W1384
SO3588 6 Sense Slope 7:99 AL5377 W1384
SO6478 7 Search Stop 2:39 AL1803 W1384
49
SO4088 8 Smile Stew 0:28 AL1803 W1384
SO4779 9 Bump Beam 6:63 AL1803 W1384
SO0859 10 Receive Reward 1:77 AL1803 W1384
SO3772 11 Sell Slope 1:37 AL1803 W1384
SO7864 12 Misunderstand Meal 1:89 AL1803 W1384
SO7597 13 Cough Range 5:51 AL1803 W1099
SO0073 14 Zipper Camp 8:12 AL1803 W1099
SO5223 1 Heat Vegetable 2:59 AL1803 W1099
SO1528 2 Clouds Disgust 1:34 AL5392 W1099
SO6312 3 Touch me 3:83 AL5392 W1099
SO7996 4 Attack Uncle 9:81 AL5392 W1099
SO3087 5 Determine Dress 8:50 AL5392 W1099
Recommend
SO6947 6 Direction 6:30 AL5392 W1099
SO3981 7 Scream Birthday 7:59 AL5392 W1099
SO9372 8 Fax Secretary 5:41 AL5392 W1099
SO2585 9 Process Road 0:58 AL5392 W1099
SO3545 1 Squash Pickle 2:07 AL5392 W1099
SO7466 2 Diagnose Bomb 8:03 AL5392 W1017
SO7763 3 Experiment Authority 4:58 AL7282 W1017
SO8381 4 Finalize Plantation 0:24 AL7282 W1017
SO3969 5 Help me 9:58 AL7282 W1017
SO1657 6 Elite Edge 5:19 AL7282 W2383
SO5914 7 Pushy Pollution 8:23 AL7282 W2383
SO9667 8 Disillusioned Donkey 8:62 AL7282 W2383
SO1319 9 Uppity Underwear 6:27 AL7282 W2383
SO2713 1 Ad Aunt 8:39 AL725 W2383
SO2932 1 Tacit Tooth 7:23 AL725 W2383
SO2873 2 Married Morning 0:14 AL8817 W3370
SO3144 3 Fallacious Fuel 0:86 AL8817 W3370
SO7803 4 Massive Mass 1:25 AL9098 W3370
SO1986 5 Careless Chair 3:54 AL9098 W3370
SO7525 6 It was the dog 9:44 AL9098 W3370
50
SO6298 7 Smoke Shame 1:51 AL3881 W3370
SO1895 8 Crate Chance 3:48 AL3881 W3370
SO9648 9 Square Shop 2:47 AL3881 W4099
SO7926 10 Juice Jewel 9:38 AL4688 W6369
SO4249 11 Blow Baseball 5:85 AL4688 W3386
SO1891 12 Business Beam 6:68 AL4688 W126
SO4994 13 Stomach Shirt 5:92 AL4688 W1262
51
2.4 Sample Queries for Database
Once a database has been developed, data must be retrieved using formal queries. We
will show a few simple SQL queries of our database before getting into more complex queries.
SELECT * FROM ARTIST;
This query would result in all the columns from the ARTIST table being extracted. The
SELECT command allows for the extraction of information directly from a table and the *
operator allows for the retrieval of all columns. FROM specifies where the data will be pulled
from, with ARTIST being the relation we’re querying in this example. The query, in English, reads
as: Select all attributes from the table Artist.
We can also be much more specific in the data we wish to retrieve from our database.
We can choose which fields we want to view.
SELECT album_name, date_released FROM ALBUM WHERE album_ID = ‘12345’;
In Relational Algebra, the equivalent expression is the following:
π album_name, date_released (σ album_ID=’123456’ (Album))
Using a Select operation, we are able to narrow down the results to only those Albums
with the ID being equivalent to ‘123456’, but using a Project operation we only retrieve two
columns: album_name and date_released.
SELECT artist_id, artist_name FROM ARTIST WHERE genre = ‘Rock’ ORDER BY DESCENDING;
Using SELECT, we specify which attributes we want retrieved, which are artist_ID and
artist_name. Then, using the FROM operator, we specify we want data FROM the ARTIST table,
but only WHERE the genre of the artist is ‘Rock’. This will output all Rock Artists. In SQL, the
52
result set can also be ordered to be more appealing or for a specific purpose, as shown in the
example with ORDER BY DESCENDING.
In the case where a count is required, the following query is an example of this.
SELECT COUNT (*) FROM ALBUM;
This query would return the amount of albums within our ALBUM entity. This information
could be useful for production purposes.
The entire purpose of this section was to display how powerful queries can be. Practically
anything and everything can be retrieved from the database with the correct queries. But these
were very simple examples. In the following sections, examples queries are shown that are far
more complex and expressive.
2.4.1 Design of Queries
The following list are ten non-trivial sample queries that are designed specific to our
database. These are great examples to show the expressiveness of data retrieval through queries
and will be later written using three formal languages: Relational Algebra, Tuple Relational
Calculus, and Domain Relational Calculus.
1. Find all active artists who have recorded at Amoeba Studios.
2. Find all artists who have recorded at least two albums.
3. Find all writers who have only written one song.
4. Find a list of albums that contain only one song (singles).
5. Find albums that have at most three songs
6. Find the longest song.
7. Find the least expensive studio.
53
8. Find all artists who have recorded at all studios.
9. Find the albums that have been purchased by every buyer.
10. Find the most worked on album between Jan 10th 2011 and October 3rd, 2013.
2.4.2 Relational Algebra Expressions
Relational Algebra is procedural language, comprised of a set of operations used for
defining queries to retrieve data stored in a Relational Database. A sequence of these operations
is a relational algebra expression, whose output is a relation that contains data queried from a
database. Below are ten query examples from our database written using Relational Algebra.
πArtist_name (σStudio_name = “Amoeba“ (Studio*Album * (σEnd_date = null(Artist*Contract)))))
A1 ← (Artist * Album)
A2 ← (Artist * Album)
πA1.artist_Name ( σ A1.artistID = A2.artistID ∧ A1.albumID ≠ A2.albumID (A1 × A2) )
s1 ← (Song * Writer)
s2 ← (Song * Writer)
π s1.fname, s1.lname (s1 − π s1.* ( σ s1.writerID = s2.writerID (s1 × s2)))
S1 ← Song
S2 ← Song
NotSingles ← πs1.* (σ s1.song_ID ≠ s2.song_ID ∧ s1.album_ID = s2.album_ID (s1 × s2))
54
πAlbum_Name (Album * (π album_ID (Song - NotSingles )))
π album_ID ( σ COUNT_song_ID <= 3 (album_ID COUNT song_ID ( Song * Album ))
S1←Song
S2←Song
π s1.song_name, s1.song_length ( σ s1.song_length > s2.song_length (S1 × S2) )
S1←Studio
S2←Studio
Expensive ← ( π s1.* ( σ s1.hourlyCost > s2.hourlyCost (S1 x S2) ) )
π studio_name, hourly_Cost ( Studio - Expensive )
π artist_name ( Artist * (( π artist_ID, studio_ID (Album)) ÷ ( π studio_ID (Studio)))
π album_name, album_ID ( π albumID, buyer_ID (Album * SoldThrough * Transaction) ÷ ( π buyer_ID

(Buyer) )
A1← σ start_date < 10/3/2013 ∧ end_date > 1/10/2011 (Album)

A2← σ start_date < 10/3/2013 ∧ end_date > 1/10/2011 (Album)
π a1.album_name ( σ a1.hrs_worked > a2.hrs_worked ( A1 × A2 ) )
55
2.4.3 Tuple Relational Calculus Expressions
Tuple Relational Calculus is a non-procedural language used to define what information
to retrieve from a database, but not how to retrieve it. Tuple Relational Calculus expressions
utilize variables that range over tuples from relations in a Relational Database. These expressions
are based on predicate logic, which make use of the Existential (∃) and Universal (∀) quantifiers.
Below are ten example queries written using Tuple Relational Calculus.
{ t | Artist(t) ∧ (∃c) (contract(c) ∧ c.end_date = null ∧ t.contract_id = c.contract_id ∧ (∃a ∃s)

(Album(a) ∧ Studio(s) ∧ a.artist_ID = t.artist_ID ∧ a.studio_ID = s.studio_ID ∧ s.studio_name =
“Ameoba Studios“))}
{ t | Artist(t) ∧ (∃A1,∃A2) (Album(A1) ∧ Album(A2) ∧ A1.albumID ≠ A2.albumID ∧

A1.artistID = t.artist_ID ∧ A2.artistID = t.artist_ID ) }
{ w | Writer(w) ∧ (∃s1) ( Song(s1) ∧ s1.writerID = w.writer_ID ∧ ¬(∃s2) (song (s2) ∧

s2.writer_ID = w.writer_ID ∧ s2.song_ID ≠ s1.song_ID ) ) }
{ a | Album(a) ∧ (∃s1) ( Song(s1) ∧ s1.album_ID = a.album_ID ∧ ¬(∃s2) (song(s2) ∧

s2.album_ID = a.album_ID ∧ s2.songID ≠ s1.song_ID))}
{ a | album(a) ∧ ¬(∃s)(Song(s) ∧ s.album_ID = a.album_ID ∧ s.track_number >= 4) }
56
{ s | Song(s) ∧ ¬ (∃s2) (Song(s2) ∧ s2.length > s.song_length) }

When you move in the negation:
{ s | Song(s) ∧ (∀s2) ¬(Song(s2) ^ s2.length > s.song_length) }
{ s | Song(s) ∧ (∀s2) (¬Song(s2) ∨ ¬(s2.length > s.song_length) ) }
{ s | Song(s) ∧ (∀s2) (¬Song(s2) ∨ (s.2length <= s.song_length) ) }
{ s | Song(s) ∧ (∀s2) (Song(s2) → s2.length <= s.song_length ) ) }
{ s | Studio(s) ∧ ¬(∃s2) ( Studio(s2) ∧ s2.hourly_cost < s.hourly_cost) }

When you move in the negation:
{ s | Studio(s) ∧ (∀s2) (¬Studio(s2) ∨ ¬(s2.hourly_cost < s.hourly_cost) }
{ s | Studio(s) ∧ (∀s2) (¬Studio(s2) ∨ (s2.hourly_cost >= s.hourly_cost) }
{ s | Studio(s) ∧ (∀s2) (Studio(s2) → (s2.hourly_cost >= s.hourly_cost) }
{ t | Artist(t) ∧ (∀s) (Studio(s) → (∃a) ( Album(a) ∧ a.artist_ID = t.artist_ID ∧ a.studio_ID =

s.studio_ID ) ) }
{ a | Album(a) ∧ (∀b) ( Buyer (b) → (∃t ∃s) (Transaction(t) ∧ Sold_Through(s) ∧ s.transaction_ID =

t.transaction_ID ∧ s.album_ID = a.album_ID ∧ t.buyerID = b.buyerID ) ) }
{ a | Album(a) ∧ a.start_date < 10/3/2013 ∧ a.end_date > 1/10/2011 ∧ ¬(∃ a2) (a2.hrs_worked >
a.hrs_worked ∧ a2.start_date < 10/3/2013 ∧ a2.end_date > 1/10/2011) }
2.3.3 Domain Relational Calculus Expressions
Domain Relational Calculus is very similar to Tuple Relational Calculus. It is also a non-
procedural language used to define what information to retrieve from a database, but not how
57
to retrieve it. These expressions are also based on predicate logic, which make use of the
Existential (∃ ) and Universal (∀ ) quantifiers. The main difference is that Domain Relational
Calculus and Tuple Relational Calculus is that Domain Relational Calculus uses variables that
range over domains of attributes, not tuples. Below are ten example queries written using Tuple
Relational Calculus.

{ <aID, cID, n> | Artist(aID, cID, n, _) ∧ contract(cID, _, _, !=null) ∧ (∃s ∃a) (Studio(s, “Amoeba
Studios”, _, _, _, _, _, _, _, _) ∧ Album(_, _, _, _, aID, s, _, _, _)}
{ < n, aID > | Artist(aID, _, n, _) ∧ (∃A1,∃A2) (Album(A1, _, _, _, aID, _, _, _, _) ∧ Album(A2, _, _, _,

aID, _, _, _,) ∧ A1!=A2}

{ w | Writer(w, _, _, _, _, _, _, _, _, _) ∧ (∃s1) ( Song(s1, _, _, _, _, w) ∧ ¬(∃s2)( Song(s2, _, _, _, _,
w) ∧ s1 != s2))}

{ a | Album(a, _, _, _, _, _, _, _, _) ∧ (∃s1) ( Song(s1, _, _, _, _, w) ∧ ¬(∃s2)( Song(s2,_,_, _,_,w) ∧
s1 != s2))}

{ aID | Album(aID, _, _, _, _, _, _, _, _) ∧ ¬( Song( _, >= 4, _, _, aID, _) }

{ <N,L> | ( Song( _, _, N, L, _, _) ∧ ¬(∃L2)( Song( _, _, N, L2, _, _) ∧ L2 > L))}

{ < n, c > | Studio( _, n, _, _, _, _, _, _, _, c) ∧ ¬(∃c2) (Studio( _, n, _, _, _, _, _, _, _, c2) ∧ c2 > c ) ) }
58
{ < aID, n > | Artist( aID, _, n, _) ∧ (∀s) (Studio(s, _, _, _, _, _, _, _, _, _) → (∃a) Album(_, _, _, _,
aID, s, _,_, _))}

{ < aID, n > | Album( aID, n, _, _, _, _, _, _, _) ∧ (∀b) (Buyer(b, _, _) → (∃ tID) Transaction(tID, b, _)
∧ SoldThrough(tID, aID, _) ) }
{ < a, aID, hw > | Album( aID, n, _, _, _, _, < 10/3/2013, > 1/10/2011, hw) ∧ ¬(∃ hw2) (Album( aID,
n, _, _, _, _, < 10/3/2013, > 1/10/2011, hw2) ∧ hw2 > hw ) }
59
Phase 3: Implementation of Relational Database
3.1 Relation Normalization
3.1.1 Anomalies
For relations that have yet to be normalized, it is possible to encounter a number of issues.
The main issues are insertion, deletion, and modification anomalies.
Insertion Anomalies
For poorly defined relation schema, insertion anomalies are common. For example, if
our Album table contained all attributes for the Studio where each album was recorded at, and
we were going to insert a new Album in the Album table, we would need to make sure that the
Studio attributes were consistent between Album tuples. This would also make it difficult to
insert a new Studio into the database since there are no Albums that were recorded there yet.
The only workaround for this issue is to insert NULL values in the attributes for the Album, which
could lead to integrity constraint issues, or to normalize the relation, which will be discussed
later.
Deletion Anomalies
The main issue with deletion anomalies is connected to the second insertion anomaly. If
we were to delete an Album from the Album table that could possibly be the only Album that
was recorded at a particular Studio, the information that once existed for that Studio would be
completely lost.
60
Modification Anomalies
Update Anomalies are when, in a poorly designed schema, updating a value in one tuple
can cause inconsistencies across many other tuples in a relation. For example, if somebody were
to change the value of an attribute of a Studio, all Album tuples that contain data for this Studio
would need to be updated to reflect this new information. If this is not done correctly, massive
data inconsistencies can result from this.
3.1.2 Normalization
Normalization is the process of analyzing database relations in order to have them
conform to certain degree of normal, aimed at reducing redundant data and data anomalies. The
normalization procedure consists of a series of tests done against relations, looking for
opportunities to decompose the relations when necessary to minimize potential data issues.
The goal of normalization is done to minimize the amount of data redundancy that
occurs, as well as to minimize insertion, deletion, and update anomalies, as was previously
described. But there are varying degrees of normalization. The main types of normalization are
as follows: First Normal Form, Second Normal Form, Third Normal Form, and Boyce-Codd
Normal Form.
First Normal Form
First Normal Form, or 1NF, states that the domain of a particular attribute must only be
atomic values, and that the value of any attribute must be a single value from the domain of that
attribute. This does not allow for having a set of values, tuple of values, or a combination of the
two. This also does not allow for nested relations. 1NF is now considered to be a part of the
61
definition of a relation in the relational model, were multi-valued attributes, and composite
values are not allowed.
Second Normal Form
Second Normal Form, or 2NF, utilizes the concept of full functional dependency. The
functional dependency A → B is described as having full functional dependency if upon
discarding any attribute C from A means that the dependency does not hold. So for relations that
have primary keys containing more than one attribute, any non-key attribute should not be
functionally dependent on a part of the primary key. 2NF must also satisfy 1NF.
For example, if we were to have a member tuple where {SSN, address} → fname is a
functional dependency. If we removed address from the dependency and it still held as true,
then it would not conform to 2NF.
Third Normal Form
Third Normal Form, or 3NF, utilizes the concept of transitive dependency. The functional
dependency between A → B in a relational schema T is a transitive dependency if there is a set of
attributes C in T that is neither a candidate key or a subset of any key of T, as well as both A → C
and C→B. In other words, no relation should have any non-key attributes functionally dependent
on another single non-key attribute, or set of non-key attributes. 3NF must also satisfy 1NF and
2NF.
62
Boyce-Codd Normal Form
Boyce-Codd Normal Form, or BCNF, is similar to 3NF, but is much more strict and
stronger. For relational schema R to be in BCNF it must be the case such that, if whenever a
nontrivial functional dependency X → A holds in R, then X is a super key of R. The definition of
BCNF only differs slightly from 3NF since the condition of 3NF which would allow A to be prime is
not within the definition of BCNF. The absence of this condition makes BCNF a stronger normal
form than 3NF.
3.1.3 Relation Normalization
Upon designing this relational database, we followed the 1NF rule that multi-valued
attributes and composite values are not allowed. Our initial E-R Model design did not contain any
multi-valued attributes or composite values to begin with, so upon converting the E-R model to
Relational Model, the adherence to 1NF came naturally from our design.
Following the Normalization principles allowed us eliminate redundant information, such
as for the Buyer relation. In early design stages, Buyer information was stored in each
Transaction tuple, which created a potential problem with inconsistent data. But by
decomposing the Transaction relation, we were able to create a Buyer relation that would
centralize all Buyer information as and eliminate any redundant and inconsistent data.
The only potential modification anomaly that is currently present in our database is how
our songs are stored in the Song relation. Each song tuple holds is track number, which signifies
its place in the track listing of its respective album. This information must be stored in this
63
manner, so to minimize any problems, either a procedure will be created to correct any potential
problems, or this will be handled on the back-end of the GUI.
3.2 SQL *Plus
3.2.1 Main Purpose
SQL *Plus is a powerful, yet simple, command-line program that allows the user to
execute commands that allow for the creation of Oracle database components, as well as the
ability to maintain and update an Oracle database. SQL *Plus also allows for the execution of
SQL, and PL/SQL statements for querying, updating, or deleting data, as well as to create
procedures or schema objects.
3.2.2 Oracle Schema Objects
Schema objects are logical structures of data in a database. Oracle allows many
different types of schema objects for their relational databases. Some examples of schema
objects are dimensions, sequences, synonyms, and clusters. The main objects that we are using
so far and the objects we plan to use are listed as follows:
Tables
Tables are the basic structures used by databases to store data. They are composed of
tuples and attributes.
Syntax:
CREATE TABLE tableName

{
columnName1 dataType constraint1 constraint2 …,
columnName2 dataType constraint1 constraint2…,
64
…
CONSTRAINT constraintName1 PRIMARY KEY (columnName, columnName,…),
CONSTRAINT constraintName2 FOREIGN KEY (columnName) REFERENCES tableName
(columnName),
…
CONSTRAINT constraintName3 CHECK(condition)
}
Views
A view is a logical, or virtual, table that is based on a stored query that gets data from
already existing base tables. Views are useful in the case of building GUI’s, as the application can
pull its information from views, rather than base tables.
Syntax:
CREATE VIEW viewName AS

SELECT columnNames
FROM tableName
WHERE condition
Stored procedures
Stored procedures are a set of SQL statements that, together, perform a specific task
on the database. They can take parameters, similar to functions, and can be used to encapsulate
a set of operations.
Syntax:
CREATE OR REPLACE PRODEDURE procedureName

IS
BEGIN
--CODE HERE
END;
65
Triggers
Triggers are procedures that are implicitly executed whenever an insert, delete, or
update command is executed on the database.
Syntax:
CREATE TRIGGER triggerName

triggerTime triggerEvent
ON tableName FOR EACH ROW
triggerBody
Indexes
An index is an object that contains an entry for each value that every indexed column
contains. Indexes allow for direct and fast access to rows in the database, which makes queries
perform much more efficiently.
Syntax:
CREATE INDEX indexName

ON tableName (columnName)
66
3.3 Relation Schema and Data
3.3.1 Contract
CONTRACT_ID NOT NULL VARCHAR2(10)

ALBUM_TERMS NUMBER(5)
START_DATE DATE
END_DATE DATE
CONTRACT_ID ALBUM_TERMS START_DATE END_DATE
------------------- -------------------- ----------------- --------------
C00001 10 23-AUG-10
C17813 1 22-APR-10 01-APR-11
C44086 3 29-MAY-10 01-MAY-13
C11328 5 14-APR-11
C4752 3 06-DEC-14
C6789 2 20-SEP-10 01-SEP-12
C55390 5 22-OCT-13
C85862 6 17-OCT-12
C34728 2 08-APR-14
C93471 4 22-NOV-12
C50643 2 08-MAR-10 01-MAR-12
C19760 1 11-NOV-14
C53622 1 04-SEP-11 01-SEP-12
C42579 2 18-JUN-12 01-JUN-14
C96710 4 05-APR-14
C67044 5 23-AUG-10
3.3.2 Artist
ARTIST_ID NOT NULL VARCHAR2(10)

CONTRACT_ID NOT NULL VARCHAR2(10)
ARTIST_NAME NOT NULL VARCHAR2(60)
GENRE VARCHAR2(20)
ARTIST_ID CONTRACT_ID ARTIST_NAME GENRE

------------- ---------------------- -------------------- --------------------
A17813 C17813 Solomon Folk
A44086 C44086 Bell Rock
A11328 C11328 Hilda Rock
A4752 C4752 Baker Metal
A6789 C6789 Jamalia Classic
A55390 C55390 Linus Folk
A85862 C85862 Steven Rock
A34728 C34728 Colette Alternative
A93471 C93471 Adena Folk
A50643 C50643 Angela Rock
A19760 C19760 Ralph SoftRock
A53622 C53622 Eliana SoftRock
A42579 C42579 Chanda Alternative
A96710 C96710 Yardley Folk
A67044 C67044 Shaman Rock
A00001 C00001 Queen Rock
67
3.3.3 Composed_Of

SSN NOT NULL VARCHAR2(9)
ARTIST_ID SSN
------------- ---------------
A11328 030529568
A11328 230405066
A11328 237095808
A17813 094103269
A17813 095128618
A17813 115807312
A17813 390796801
A17813 718402914
A17813 799516609
A17813 898636127
A19760 934669708
A34728 065291852
A34728 326337324
A34728 681991076
A34728 794071975
A42579 018387601
A42579 647906445
A42579 737347478
A44086 316632012
A44086 466691455
A44086 706388832
A44086 913287463
A4752 033390750
A4752 174635988
A4752 350826131
A4752 522646239
A4752 555698350
A4752 752640200
A4752 966728262
A50643 315185134
A50643 493540743
A53622 103180494
A53622 130871020
A53622 519055209
A53622 648377849
A53622 689619138
A55390 022628780
A55390 359684742
A55390 445089490
A55390 694775842
A55390 727706860
A67044 055923612
A67044 196457896
A67044 249648859
A67044 355422240
A67044 372273921
A67044 539638320
A67044 703998621
A67044 800960314
A67044 815096438
A67044 985148004
A6789 060968225
A6789 080699386
A6789 380384098
A6789 704113574
A6789 936507878
A85862 297665184
A85862 727431600
A85862 835471116
A93471 238442093
68
3.3.4 Member

FNAME NOT NULL VARCHAR2(45)
LNAME VARCHAR2(45)
PHONE VARCHAR2(45)
ADDRESS VARCHAR2(50)
CITY VARCHAR2(45)
STATE VARCHAR2(20)
ZIP VARCHAR2(10)
INSTRUMENT VARCHAR2(60)
START_DATE DATE
END_DATE DATE
69
3.3.5 Album
ALBUM_ID NOT NULL VARCHAR2(10)

ALBUM_NAME NOT NULL VARCHAR2(60)
DATE_RELEASED DATE
UNIT_PRICE NUMBER(6,2)
STUDIO_ID NOT NULL VARCHAR2(10)
START_DATE DATE
END_DATE DATE
HRS_WORKED NUMBER(5)
ALBUM_ID ALBUM_NAME DATE_REL UNIT_PRICE ARTIST_ID STUDIO_ID START_DAT END_DATE HRS_WORKED

------------- --------------------- --------------- ------------------ ----------------- --------------- ---------------- ------------------- ----------------------
AL3626 Microscope 08-FEB-11 9.99 A17813 S1262 01-JAN-11 03-FEB-11 200
AL4620 Midget 07-FEB-13 12.99 A44086 S8241 17-APR-12 02-FEB-13 200
AL2151 Credit Card 18-JUL-11 3.99 A11328 S5458 24-APR-11 11-JUL-11 300
AL7741 System 15-APR-15 9.99 A4752 S5894 15-DEC-14 15-MAR-15 1000
AL2381 Observation 26-AUG-11 7.99 A6789 S6310 15-FEB-11 20-AUG-11 200
AL5377 Fork 15-JAN-15 24.99 A55390 S4552 14-SEP-14 26-DEC-14 300
AL5780 T-shirt 24-JAN-13 2.99 A85862 S1384 18-NOV-12 14-JAN-13 500
AL1803 Surgeon 15-DEC-14 1.99 A34728 S1099 17-NOV-14 01-DEC-14 700
AL5392 Wisdom 25-MAY-14 7.99 A93471 S1017 13-MAR-13 18-MAY-14 800
AL7282 Log 16-AUG-11 8.99 A50643 S2383 07-JUN-11 08-AUG-11 70
AL725 Laser 24-DEC-15 9.99 A19760 S3370 10-OCT-15 12-DEC-15 59
AL8817 Tap 17-JAN-12 9.99 A53622 S4099 25-SEP-11 01-JAN-12 300
AL9098 Critic 24-DEC-13 12.99 A42579 S6369 08-AUG-13 12-DEC-13 250
AL3881 Throne 19-JUN-15 14.99 A96710 S3386 02-FEB-15 06-JUN-15 50000
AL4688 Hazard 19-JAN-13 16.99 A67044 S126 05-OCT-12 01-JAN-13 300
AL0001 Queen 08-FEB-11 9.99 A00001 S1262 01-JAN-11 03-FEB-11 200
AL0002 Queen II 07-FEB-13 12.99 A00001 S8241 17-APR-12 02-FEB-13 200
AL0003 Sheer Heart Attack 18-JUL-11 3.99 A00001 S5458 24-APR-11 11-JUL-11 300
AL0004 A Night at the Opera 15-APR-15 9.99 A00001 S5894 15-DEC-14 15-MAR-15 1000
AL0005 Day at the Races 26-AUG-11 7.99 A00001 S6310 15-FEB-11 20-AUG-11 200
AL0006 News of the World 15-JAN-15 24.99 A00001 S4552 14-SEP-14 26-DEC-14 300
AL0007 Jazz 24-JAN-13 2.99 A00001 S1384 18-NOV-12 14-JAN-13 500
AL0008 The Game 15-DEC-14 1.99 A00001 S1099 17-NOV-14 01-DEC-14 700
AL0009 Hot Space 25-MAY-14 7.99 A00001 S1017 13-MAR-13 18-MAY-14 800
AL0010 The Works 16-AUG-11 8.99 A00001 S2383 07-JUN-11 08-AUG-11 70
AL0011 A Kind of Magic 24-DEC-15 9.99 A00001 S3370 10-OCT-15 12-DEC-15 59
AL0012 The Miracle 17-JAN-12 9.99 A00001 S4099 25-SEP-11 01-JAN-12 300
AL0013 Innuendo 24-DEC-13 12.99 A00001 S6369 08-AUG-13 12-DEC-13 250
AL0014 Made in Heaven 19-JUN-15 14.99 A00001 S3386 02-FEB-15 06-JUN-15 50000
AL0015 Live Killers 19-JAN-13 16.99 A00001 S126 05-OCT-12 01-JAN-13 300
AL4689 Blah 19-JAN-14 16.99 A67044 S126 05-OCT-13 01-JAN-14 40
70
3.3.6 Sold_Through
TRANSACTION_ID NOT NULL VARCHAR2(10)

BATCH_UNITS NUMBER(15)
TRANSACTION_ID ALBUM_ID BATCH_UNITS

----------------------- -------------- -------------------
T17813 AL3626 200000
T44086 AL4620 250000
T11328 AL2151 150000
T4752 AL7741 10000
T6789 AL2381 500000
T55390 AL5377 25000
T85862 AL5780 1000
T34728 AL1803 100
T93471 AL5392 55000
T50643 AL7282 230000
T19760 AL725 100000
T53622 AL8817 1500
T42579 AL9098 2000
T96710 AL3881 250
T67044 AL4688 100000
T00001 AL3626 100
T00001 AL4620 100
T00001 AL2151 100
T00001 AL7741 100
T00001 AL2381 100
T00001 AL5377 100
T00001 AL5780 100
T00001 AL1803 100
T00001 AL5392 100
T00001 AL7282 100
T00001 AL725 100
T00001 AL8817 100
T00001 AL9098 100
T00001 AL3881 100
T00001 AL4688 100
T00001 AL0001 100
T00001 AL0002 100
T00001 AL0003 100
T00001 AL0004 100
T00001 AL0005 100
T00001 AL0006 100
T00001 AL0007 100
T00001 AL0008 100
T00001 AL0009 100
T00001 AL0010 100
T00001 AL0011 100
T00001 AL0012 100
T00001 AL0013 100
T00001 AL0014 100
T00001 AL0015 100
T00001 AL4689 100
71
3.3.7 Transaction
TRANSACTION_ID NOT NULL VARCHAR2(10)

BUYER_ID NOT NULL VARCHAR2(10)
TDATE DATE
TRANSACTION_ID BUYER_ID TDATE

-------------------------- ------------- --------------
T17813 B00001 05-OCT-15
T44086 B00005 26-JUN-14
T11328 B00003 25-JUL-14
T4752 B00004 09-SEP-14
T6789 B00005 23-SEP-15
T55390 B00006 21-MAR-15
T85862 B00004 21-NOV-15
T34728 B00005 18-SEP-14
T93471 B00006 10-APR-15
T50643 B00005 05-AUG-14
T19760 B00005 01-APR-15
T53622 B00001 21-JAN-14
T42579 B00004 30-NOV-13
T96710 B00006 19-SEP-15
T67044 B00001 05-OCT-15
T00001 B00002 01-NOV-15
3.3.8 Buyer
BUYER_ID NOT NULL VARCHAR2(10)

BUYER_NAME NOT NULL VARCHAR2(45)
PHONE VARCHAR2(11)
CITY VARCHAR2(25)
STATE VARCHAR2(20)
ZIP VARCHAR2(6)
72
3.3.9 Song
SONG_ID NOT NULL VARCHAR2(10)

TRACK_NUMBER NUMBER(5)
SONG_NAME VARCHAR2(60)
SONG_LENGTH TIMESTAMP(6)
WRITER_ID NOT NULL VARCHAR2(10)
73
74
3.3.10 Studio
STUDIO_ID NOT NULL VARCHAR2(10)

STUDIO_NAME NOT NULL VARCHAR2(45)
PHONE VARCHAR2(11)
CITY VARCHAR2(25)
STATE VARCHAR2(20)
ZIP VARCHAR2(10)
OPEN_DATE DATE
CLOSE_DATE DATE
HOURLY_COST NUMBER(6,2)
75
3.3.11 Writer
WRITER_ID NOT NULL VARCHAR2(10)

FNAME VARCHAR2(45)
LNAME VARCHAR2(45)
PHONE VARCHAR2(11)
CITY VARCHAR2(25)
STATE VARCHAR2(20)
ZIP VARCHAR2(6)
START_DATE DATE
END_DATE DATE
76
3.4 SQL Queries
The following queries are translated from the Phase 2 relational algebra and relational
calculus queries.
SELECT DISTINCT art.artist_name

FROM artist art, studio s, album al, contract con
WHERE art.contract_id = con.contract_id AND
con.end_date is null AND
al.artist_id = art.artist_id AND
al.studio_id = s.studio_id AND
s.studio_name = 'Cursus Inc.'
Output:
Artist_name
Shaman
Queen
SELECT DISTINCT a.artist_id, a.artist_name

FROM artist a
INNER JOIN album al
ON a.artist_id = al.artist_id
WHERE a.artist_id IN (
SELECT artist_id
FROM album
GROUP BY artist_id
HAVING COUNT (*) >= 2
)
Output:
Artist_ID Artist_Name
A1010 Led Zeppelin
A67044 Shaman
A00001 Queen
77
SELECT DISTINCT w.writer_id, w.fname, w.lname

FROM writer w
INNER JOIN song s
ON w.writer_id = s.writer_id
WHERE w.writer_id IN (
SELECT writer_id
FROM song
GROUP BY writer_id
HAVING COUNT (*) = 1
)
Output:
Writer_ID Fname Lname
W127 Rhea Amaya
W1264 Rhea Amaya
SELECT distinct al.album_id, al.album_name

FROM album al
INNER JOIN song s
ON al.album_id = s.album_id
WHERE al.album_id IN (
SELECT album_id
FROM song
GROUP BY album_id
HAVING COUNT (*) = 1
)
Output:
Album_ID Album_Name
AL1313 Coda
AL2020 I Am the Greatest Ever
AL725 Laser
AL0004 A Night at the Opera
78
SELECT DISTINCT al.album_id, al.album_name

FROM album al
INNER JOIN song s
ON al.album_id = s.album_id
WHERE al.album_id IN (
SELECT album_id
FROM song
GROUP BY album_id
HAVING COUNT (*) >= 3
)
Output:
Album_ID Album_Name
AL7741 System
AL5377 Fork
AL7282 Log
AL1803 Surgeon
AL3881 Throne
AL3627 Microscope
AL4688 Hazard
AL4621 Midget
AL2381 Observation
AL2151 Credit Card
AL5392 Wisdom
SELECT s.song_id, s.song_name

FROM song s
WHERE NOT EXISTS (
SELECT s2.song_id, s2.song_length
FROM song s2
WHERE s2.song_length > s.song_length
)
Output:
Song_ID Album_Name
S0010 Death on Two Legs
79
SELECT s.studio_id, s.studio_name

FROM studio s
WHERE NOT EXISTS (
SELECT s2.HOURLY_COST
FROM _studio s2
WHERE s2.hourly_cost < s.hourly_cost )
Output:
Studio_ID Studio_Name
S8241 Pede Malesuada Industries
SELECT a.artist_id, a.artist_name

FROM artist a
INNER JOIN album al
ON al.artist_id = a.artist_id
GROUP BY a.artist_id, a.artist_name
HAVING COUNT (distinct al.studio_id) = (SELECT COUNT (*) FROM studio)
Output:
Artist_id Artist_name
A00001 Queen
SELECT b.buyer_id, b.buyer_name

FROM buyer b
INNER JOIN transaction t
ON b.buyer_id = t.buyer_id
INNER JOIN sold_through st
ON st.transaction_id = t.transaction_id
GROUP BY b.buyer_id, b.buyer_name
HAVING COUNT (DISTINCT st.album_id) = (SELECT COUNT (*) FROM album)
Output:
Buyer_id Buyer_name
B00002 Kmart
80
CREATE OR REPLACE VIEW transaction_info AS

SELECT a.album_id, a.album_name, a.hrs_worked
FROM album a
WHERE a.start_date < DATE '2013-10-03'
AND a.end_date > DATE '2011-01-10'
AND NOT EXISTS (
SELECT a2.hrs_worked
FROM album a2
WHERE a2.start_date < DATE '2013-10-03'
AND a2.end_date > DATE '2011-01-10'
AND a2.hrs_worked > a.hrs_worked
)
Output:
Album ID Album Name Hrs_Worked
AL56 Physical Graffiti 8000
11. For all transactions, find the total number of album units purchased, as well as the total cost
for each transaction.
SELECT t.transaction_id, b.buyer_name, (

SELECT sum(st.batch_units)
FROM maal_sold_through st
WHERE st.transaction_id = t.transaction_id
) AS Total_units, (
SELECT sum(al.unit_price * st.batch_units)
FROM album al
INNER JOIN sold_through st
ON al.album_id = st.album_id
WHERE st.transaction_id = t.transaction_id
) AS Total_Price, t.tdate
FROM transaction t
INNER JOIN buyer b
ON b.buyer_id = t.buyer_id
Output:
Transaction_ID Buyer_name Total_units Total_price Tdate
T53622 FYE 1500 14985 21-JAN-14
T67044 FYE 100000 1699000 05-OCT-15
T00001 Kmart 3000 31326 01-NOV-15
T11328 Wal-Mart 150000 598500 25-JUL-14
T4752 Costco 10000 99900 09-SEP-14
81
T85862 Costco 1000 2990 21-NOV-15
T44086 Target 250000 3247500 26-JUN-14
T6789 Target 500000 3995000 23-SEP-15
T34728 Target 100 199 18-SEP-14
T50643 Target 230000 2067700 05-AUG-14
T19760 Target 100000 999000 01-APR-15
T55390 Amoeba Records 25000 624750 21-MAR-15
T93471 Amoeba Records 55000 439450 10-APR-15
T96710 Amoeba Records 250 3747.5 19-SEP-15
82
Phase 4: Stored Procedures, Packages, and Triggers
4.1 Oracle PL/SQL
4.1.1 What is PL/SQL?
PL/SQL, which stands for Procedural Language extensions to SQL, is a language used by
Oracle in order to add more programming ability to the SQL language for creating more complex
objects and operations. PL/SQL has procedural language attributes such as conditions and loops
and allows declaration of constants and variables, as well as functions and error handling.
Benefits of PL/SQL
PL/SQL allows for the creation of stored procedures, functions, and triggers. These
objects are very useful because:
1. They allow for database automation. Instead of having to worry about complicated
updates and deletes, procedures can be created that will handle all of this for you
automatically, instead of having to execute the individual statements in the client, which
can be less secure.
2. If a database is used by many applications written in different languages, it can then be
stored in the database and then called by any of those different applications.
3. There is less latency, or quicker response time, when stored procedures are used.
4. A procedure can assist in creating a view by allowing for a more complex type of derived
data to become available to the user. They can also be used to check for more advanced
constraints.
5. Error handling can be stored as part of the procedure.
Control Statements
PL/SQL has a number of control statements that are very useful for creating procedures.
There are three categories of PL/SQL control statements. They are:
83
1. Conditional Selection Statement: Runs different statements for different data value.
These statements are IF and CASE.
2. Loop Statements: Run the same statements with a series of different data values. The
loop statements are the LOOP, FOR LOOP, and WHILE LOOP.
3. Sequential Control Statements: Are not necessary to PL/SQL, but can be used. The
sequential control statements are GOTO and NULL.
4.1.2 PL/SQL Syntax
Stored Procedure
Stored procedures are similar to functions, in that they are a set of stored PL/SQL
statements that can be executed any number of times. These procedures can also take in
parameters. Stored procedures are helpful in that instead of explicitly running the same series of
PL/SQL statements over and over, you can just store it once and call it as many times as
necessary, which is more efficient.
Syntax:
CREATE [OR REPLACE] PROCEDURE <procedure name> [list of parameters]

IS
<Declaration Section>
BEGIN
<procedure body>
END;
Stored Functions
A stored function is a set of PL/SQL statements that can be called by a function name.
They are almost the same as a stored procedures, except a function returns a value where it is
called. A procedure may or may not return a value.
84
Syntax:
CREATE [OR REPLACE] FUNCTION <function name> [parameters]

RETURN <return datatype>;
IS
<Declaration section>
BEGIN
<Function Body>
Return <return variable>;
EXCEPTION
<Exception section>
Return <return variable>;
END;
Packages
Packages are schema objects that are used to logically group related procedures and
functions, as well as variables. Upon grouping these items into a package, an application that is
using these subprograms only needs to know the name of the subprogram and the parameters
needed for these subprograms, and does not need to know any of the specific implementation
details. This idea is similar to prototypes and function bodies in C++.
Syntax:
CREATE PACKAGE <package name> AS

<variables and their specifications>
PROCEDURE A1
PROCEDURE A2
PROCEDURE A3
END <package name>
Triggers
Triggers are programs or procedures that are stored in the database and implicitly
executed before, after, or instead of an update, insert, or deletion. This is a method of specifying
85
certain rules, and will help to enforce these rules whenever data has been modified within a
table. The below code is a sample of how trigger syntax looks.
Syntax:
CREATE TRIGGER <trigger name>

<BEFORE | AFTER | INSTEAD OF>
<INSERT | DELETE | UPDATE>
<OF column name>
ON <table name>
FOR EACH ROW
WHEN <conditions>
BEGIN
<Desired statement go here>
END;
Cursor
A Cursor is a temporary work space created in the system memory when SQL
statements are executed. Cursors allows you to give a select statement a name, so that you can
then access the information retrieved in that select statement in some kind of procedure. There
are two types of cursors, which are explicit and implicit. Both have the same functionality but are
different in the way they are accessed.
Syntax:
CURSOR <cursor name> IS

<Desired FUNCTIONALITY>
BEGIN
OPEN <cursor name>
<BODY>
CLOSE <cursor name>
END;
86
4.2 MS SQL Server and MySQL Stored Procedures
4.2.1 Microsoft SQL Server and T-SQL
Microsoft SQL Server uses an extension of SQL called T-SQL, short for Transaction-SQL,
which, similar to PL/SQL, has a number of features that are not available with SQL, including
procedural programming and variables, which allow for the creation of stored procedures.
A major feature that T-SQL has, that differs from Oracle, is that the DELETE and
UPDATE commands both allow for the inclusion of the FROM clause which allows that usage of
JOINS, making filtering records much easier, and the deletion of records far easier than in
PL/SQL.
T-SQL Procedure
The major difference between T-SQL and PL/SQL is parameter passing. PL/SQL uses IN,
OUT, and INOUT to differentiate between different types of parameters. T-SQL uses OUT,
OUTPUT, and READONLY for this purpose, which is the same idea as what Oracle implements,
but just with different syntax. Another key difference is the use of the @ to signify the use of a
variable. T-SQL also allows the use of a SELECT statement without having to use the keyword
INTO, which PL/SQL requires when assigning SELECT results into a variable. Despite these
differences, the overall structure is very similar to that of PL/SQL.
Syntax:
CREATE { PROCEDURE | PROC } [schema_name.]procedure_name

[ @parameter [type_schema_name.] datatype
[ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ]
, @parameter [type_schema_name.] datatype
[ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ] ]
[ WITH { ENCRYPTION | RECOMPILE | EXECUTE AS Clause } ]
87
[ FOR REPLICATION ]
AS
BEGIN
[declaration_section]
executable_section
END;
T-SQL Function
T-SQL Functions are very similar to T-SQL procedures, in terms of syntax and semantics.
Functions in T-SQL also make use of OUT, OUTPUT, and READONLY variable types, and their
overall structure is very similar to that of PL/SQL. The symbol @ is also used throughout for
variables and parameters.
Syntax:
CREATE FUNCTION [schema_name.]function_name

( [ @parameter [ AS ] [type_schema_name.] datatype
[ = default ] [ READONLY ]
, @parameter [ AS ] [type_schema_name.] datatype
[ = default ] [ READONLY ] ]
)
RETURNS return_datatype
[ WITH { ENCRYPTION
| SCHEMABINDING
| RETURNS NULL ON NULL INPUT
| CALLED ON NULL INPUT
| EXECUTE AS Clause ]
[ AS ]
BEGIN
[declaration_section]
executable_section
RETURN return_value
END;
88
4.2.2 MySQL Server Routines
MySQL Stored Procedure
MySQL uses what are called Routines, which are equivalent to Procedures in PL/SQL, and
are very similar to Oracle Procedures in terms of syntax and semantics, as they have the ability to
use cursors, as well as all control statements Oracle has, such as IF, ELSE, case statements, and
loops. Similar to Oracle, MySQL also allows the use of IN, OUT, and INOUT parameter passing.
And MySQL also requires that you use the keyword INTO when using SELECT in a procedure,
which Oracle also requires.
Syntax:
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...]
BEGIN
routine_body
END
MySQL Function
MySQL Functions are very similar to Oracle functions, but in MySQL you cannot use OUT
or INOUT parameters. By default, all parameters are IN and this cannot be changed. This is a
major difference between Oracle, which allows the use of all three types of parameters in
functions and procedures. MySQL functions are also only allowed to return a single value.
Syntax:
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
89
[characteristic ...]
BEGIN
routine_body
End
4.3 PL/SQL Subprogram Implementations
4.3.1 Procedures
The following procedures were created for our database. They serve various functions
and are specific to our database.
Delete Album Procedure
The following procedure is used to delete an Album and all of its Songs from their
respective tables in the correct order to avoid violating referential constraints.
CREATE OR REPLACE PROCEDURE maal_delete_album(aid IN maal_album.album_id%type)

IS
BEGIN
DELETE FROM maal_song
WHERE maal_song.album_id= maal_delete_album.aid;
DELETE FROM maal_album
WHERE maal_album.album_id = maal_delete_album.aid;
COMMIT;
END;
Delete Artist Procedure
The following procedure is used to delete an Artist from the database. In order to fully
delete an Artist from the database, all albums, songs, contract information, and transaction
information must be deleted to avoid any referential constraint problems.
90
CREATE OR REPLACE PROCEDURE maal_delete_artist_proc(aid IN maal_artist.artist_id%type)
IS
BEGIN
DELETE FROM maal_song
WHERE album_id IN
(
SELECT album_id FROM maal_song
NATURAL JOIN maal_album
WHERE artist_id = maal_delete_artist_proc.aid
);
DELETE FROM maal_composed_of
WHERE maal_composed_of.artist_id = maal_delete_artist_proc.aid;
DELETE FROM maal_member
WHERE ssn IN
(
SELECT ssn FROM maal_member
NATURAL JOIN maal_composed_of
WHERE maal_composed_of.artist_id = maal_delete_artist_proc.aid
);
DELETE FROM maal_sold_through
WHERE album_id IN
(
SELECT album_id FROM maal_sold_through
NATURAL JOIN maal_album
WHERE maal_album.artist_id = maal_delete_artist_proc.aid
);
DELETE FROM maal_album
WHERE maal_album.artist_id = maal_delete_artist_proc.aid;
DELETE FROM maal_contract
WHERE CONTRACT_ID IN
(
SELECT contract_id FROM maal_contract
NATURAL JOIN maal_artist
Where maal_artist.artist_id = maal_delete_artist_proc.aid
);
DELETE FROM maal_artist
WHERE artist_id = maal_delete_artist_proc.aid;
COMMIT;
END;
91
Album Sales and Average Revenue Procedure
The following procedure is used to get the total amount of revenue generated from an
album transaction purchased by a major retailer distributor. This procedure also retrieves the
average transaction revenue.
CREATE OR REPLACE PROCEDURE MAAL_Get_Sales_Average

IS
--DECLARE
revenue number(20);
total number(20) :=0 ;
counts number(10);
average number(20,4);
Cursor cur1 is
select transaction_id, album_id, batch_units
from maal_sold_through;
v_ST_transid maal_SOLD_THROUGH.transaction_id%type;
v_ST_albumid maal_SOLD_THROUGH.album_id%type;
v_ST_batch maal_SOLD_THROUGH.batch_units%type;
v_a_unit maal_ALBUM.unit_price%type;
Cursor cur2 is
select album_id, unit_price
from maal_album;
v_T_album maal_album.album_id%type;
v_T_unit_price maal_album.unit_price%type;
BEGIN
select count(transaction_id) into counts from MAAL_sold_through;
dbms_output.put_line( RPAD('TransID',15,' ') || RPAD('albumID',15,' ') || RPAD('Batch',15,' ')
|| RPAD('Revenue',15,' '));
dbms_output.put_line('--------------------------------------------------------------------');
OPEN cur1;
LOOP
fetch cur1 into v_ST_transid, v_ST_albumid, v_ST_batch;
exit when cur1%NOTFOUND;
OPEN cur2;
LOOP
92
fetch cur2 into v_T_album, v_T_unit_price;
exit when cur2%NOTFOUND;
if v_ST_albumid = v_T_album then
revenue := (v_T_unit_price * v_ST_batch);
end if;
End LOOP;
CLOSE cur2;
dbms_output.put_line( RPAD(v_ST_transid,15, ' ') || RPAD(v_ST_albumid,15, ' ') ||
RPAD(v_ST_batch,15,' ') || RPAD(revenue,15,' '));
total := (total + revenue);
--dbms_output.put_line( RPAD('Total Revenue', 15, ' ') || RPAD(total,15,' '));
END LOOP;
CLOSE cur1;
dbms_output.put_line('--------------------------------------------------------------------');
average := (total / counts);
dbms_output.put_line( RPAD('Average Revenue',20, ' ') || RPAD(average,15,' '));
end;
Insert Contract Procedure
The following procedure is used to insert a new Contract into the Contract relation.
CREATE OR REPLACE PROCEDURE maal_insert_contract

(
CON_ID IN VARCHAR2,
ALTERMS IN NUMBER,
SDATE IN DATE,
EDATE IN DATE
)
AS
BEGIN
INSERT INTO maal_contract
VALUES
(
CON_ID,
ALTERMS,
SDATE,
EDATE
);
END maal_insert_contract;
93
4.3.2 Triggers
Album ID Update Trigger
The following trigger is execute when you attempt to update the primary key
Album_ID. The new value will be updated everywhere else it is referenced as a foreign key.
CREATE OR REPLACE TRIGGER maal_alupdate

AFTER UPDATE OR INSERT ON maal_album
FOR EACH ROW
BEGIN
UPDATE maal_song set maal_song.ALBUM_ID = :NEW.album_id
WHERE maal_song.album_id = :OLD.album_id;
UPDATE maal_sold_through set ALBUM_ID = :NEW.album_id
WHERE maal_sold_through.album_id = :OLD.album_id;
END;
Album Info View Update Trigger
The following trigger is used when you attempt to update data on the Album_Info view
in the database. Instead of updating the view, this trigger instead uses the new values to insert
into the base tables that the view uses to draw data from.
CREATE OR REPLACE TRIGGER maal_update_album_info

INSTEAD OF UPDATE ON maal_album_info
FOR EACH ROW
BEGIN
UPDATE maal_album
SET album_id=:NEW.album_id, album_name=:NEW.album_name, date_released=
:NEW.date_released
WHERE album_id = :OLD.album_id;
UPDATE maal_song
SET track_number=:NEW.track_number, song_name=:NEW.song_name
WHERE album_id = :OLD.album_id;
COMMIT;
END;
94
Writer ID Update Trigger
This trigger is used when you attempt to update a writer_id, which is the primary key of
the Writer relation. This trigger will update this value everywhere it is referenced as a foreign key
in the database.
CREATE OR REPLACE TRIGGER maal_writer_update

AFTER UPDATE ON maal_writer
FOR EACH ROW
BEGIN
UPDATE maal_song set maal_song.writer_id = :NEW.writer_id
WHERE maal_song.writer_id = :OLD.writer_id;
END;
Delete Transaction Triggers
The following triggers are used together in order to do three things. First, upon
attempting to delete a Transaction record, all Sold_Through records associated with that specific
Transaction will also be deleted. After this has occurred, the second two triggers will be
executed, which will back up this deleted data into two log tables created specifically for this
operation.
CREATE OR REPLACE TRIGGER maal_trans_delete

BEFORE DELETE ON maal_transaction
FOR EACH ROW
BEGIN
DELETE FROM maal_sold_through
WHERE maal_sold_through.transaction_id = :OLD.transaction_id;
END;
CREATE OR REPLACE TRIGGER maal_trans_delete_log

AFTER DELETE ON maal_transaction
FOR EACH ROW
BEGIN
INSERT INTO maal_transaction_log
VALUES (:OLD.transaction_id, :OLD.buyer_id, :OLD.tdate,sysdate );
95
END;
CREATE OR REPLACE TRIGGER maal_sold_Through_delete_log

AFTER DELETE ON maal_sold_through
FOR EACH ROW
BEGIN
INSERT INTO maal_sold_through_log
VALUES (:OLD.TRANSACTION_ID, :OLD.ALBUM_ID, :OLD.batch_units, sysdate);
END;
96
Phase 5: Graphical User Interface
5.1 User Groups
5.1.1 Executive Assistants
Executive assistants group will use this database at any location in order to gather
information on new artists that have been contracted out, as well as the amount of terms they
have been assigned to. They gather this information for higher-up executives as well as for
financial purposes.
Daily Activities:
The daily activities of executive assistants are as follows:
 Retrieve contract information for artists currently working for company.
 Retrieve album information, especially cost to make album
 Assign or change studios for albums being recorded.
 Search for information needed for executives, such as financial information, and overall
information on the hiring of artists by contract.
View
The views executive assistants would need to access are as follows:
 ARTIST_CONTRACT INFO
 CURRENT_CONTRACT REPORT
 MEMBER_GEN_INFO
97
5.1.2 Artists
The artist group will use this database in order to keep informed with their current
albums and track listings, as well as their current studio that they will be working at. The group
will have the ability to view and add/update members.
Daily Activities:
The daily activities for this
 View current albums that they have released or are currently working on
 The number of albums left under their current contract with the company.
Views
The views that Artist would need to access are as follows:
 ARTIST_CONTRACT_INFO
 ALBUM_SONG
98
5.2 GUI Design and Development in Java
Both of us have had a little experience with Java prior to us starting the database project.
This allowed us to ease into the developer tool called NetBeans, which is an IDE that can be used
for application development. We began by looking into internet resources as well as online
tutorials for help on how to develop a simple user interface. Once we were able to get a few
examples down, we began to work out a layout that we would use for the database GUI. The
steps and we took are as follows:
1. We brainstormed ideas on what the user interface should allow the user to do. We then
drew several diagrams on paper and did quick implementations in NetBeans to see the
practicality of those designs and ideas. We thought of a few ideas on how to display the
information and how the user could access the information.
2. Once we found a layout that we both liked, we moved forward and began to implement
the final design in NetBeans.
3. We designed two reports for the GUI. One report is designed to print Album information,
and the other is designed for printing Contract information.
We learned that even though we had an IDE to help us navigate our GUI, it would not always
turn out the way we had hoped. There were many times where a GUI design would look fine
during development, but when we would test the application, text fields and labels would start
to move in different places.
Pulling data from a table and display it through a GUI was a main goal, and then display
information in different areas based off of their relations. We learned that if our prepared
99
statements did not currently match our data model, our queries would fail and leave us with no
information.
There are many components that can be added to the database, but for the purpose of this
project, we wanted to limit the amount information for now. This can later be fixed by adding
more.
100
5.3 Major Features
The below code is used to connect to our database, and add values to the current
selected table. We use the prepared statements to insert or update values as they are passed to
text fields in the GUI
5.3.1 Connecting to the Oracle Database
This code allows us to establish a connection to the CSUB Delphi database. We first
create a Connection class and specify the credentials needed to connect. An instance of this class
is used in the main Menu class and the function getDBConnection() is called to connect to the
database before every SQL statement execution.
101
5.3.2 Inserting into Database
The following code is an example of an insertion into the Contract and Artist tables.
This code is executed when the Save button is clicked on the Contract Information panel. This
code pulls text from all the appropriate text fields and uses a Prepared Statement to set up a SQL
Insert statement to insert a new Contract and Artist into the database.
102
5.3.3 Row Filtering For Searching
The below code is used to filter table results in the table. When a user wants to search
for a specific attribute, they can use the search text field to narrow down information. The data
is filtered by each letter entered. For example, the letter "A" will filter fields that contain the
letter "A". By typing "Ab", the search will filter fields that contain the combination "Ab" and so
on.
103
5.3.4 Assigning Studios to Albums
A major feature of our GUI is the ability to assign a Studio to an Album. Using a JDialog
form, when a user is entering in Album information, you have the ability to click on
“Change/Assign Studio” which will open the JDialog form. In this JDialog form, you can add a new
Studio if the Album was recorded at a Studio that does not currently exist in the database, or
search for the already existing Studio and click “Choose Studio” to assign that Studio to the
Album that is currently be entered or edited.
104
Through the use of static variables, whenever a JDialog is created, the JDialog is able to
access and change data in the Menu frame, which allows for simple communication between the
JDialog and the Menu frame, which allows for assigning Studios to Albums.
When focus is gained on the Menu frame, that means that the JDialog has completed
its task, and a Studio has been chosen. So it will automatically pull the string data from the static
variable that has been assigned a value in the JDialog, and it will use it to fill the proper
JTextField.
105
5.3.5 Assigning Writers to Songs
A major feature of our GUI is the ability to assign a Songwriter to a Song. Using a JDialog
form, when a user is entering in Song information, you have the ability to click on
“Change/Assign Writer” which will open the JDialog form. In this JDialog form, you can add a new
Writer if the Song was written by a Songwriter that does not currently exist in the database, or
search for the already existing Songwriter and click “Choose Writer” to assign that writer to the
song that is currently be entered or edited.
This is implemented in the exact same way as when an Album is assigned a Studio. By
using static varibles, the JDialog and the Menu frame are able to communicate and pass data
between the two objects.
106
5.3.6 Generated Reports
A major feature of our GUI is the ability to generate reports. For a generated report, the
user has the option of either selecting a single artist and displaying the contract information
related to that artist, or they can print all available contracts under the company. This
information is displayed in a separate window for readability. Our reports are generated using a
plugin for NetBeans called Jasper reports. The image below is a contract report generated for a
single contract and artist.
107
Jasper Report utilizes HashMaps in order to take in parameters to fill in the XML
reports based on a specified query. So for the Contract report, the Contract ID of the selected
artist is passed in to the report when it is generated. This allows the report to be dynamic and
change based on the users selection.
108
5.4 Outcome
In conclusion, we learned that if we had an issue that could not immediately be solved,
outside sources or communication with each other is needed. We also learned that
collaborating with a partner allows for a better design. Communication with each other is key
when designing and operating a database.
From a technical aspect, we learned how databases are designed and implemented in
great detail. We were able to explore databases from initial design, to implementation and GUI
development. This covered several large topics, and we were able to get experience in all of
them.
Members' Outcome
Outcome
Answers btw 1 & 10
(3b) An ability to analyze a problem, and identify and define

the computing requirements and specifications appropriate to Member 1: 10
its solution.
Member 2: 10
(3e) An ability to design, implement and evaluate a computer-

based system, process, component, or program to meet
desired needs. An ability to understand the analysis, design, Member 1: 10
and implementation of a computerized solution to a real-life
problem. Member 2: 10
(3f) An ability to communicate effectively with a range of

audiences. An ability to write a technical document such as a Member 1: 10
software specification white paper or a user manual.
Member 2: 10
(3j) An ability to apply mathematical foundations, algorithmic

principles, and computer science theory in the modeling and
design of computer-based systems in a way that demonstrates Member 1: 10
comprehension of the tradeoffs involved in design choices.
Member 2: 10
109

MArme ALane Phase5

Uploaded by

Copyright:

Available Formats

MArme ALane Phase5

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

MArme ALane Phase5

Uploaded by

Copyright:

Available Formats

RECORD COMPANY

Mark Armendariz and Andrew Lane

1.1 Fact-Finding Techniques and Information Gathering

1.1.1 Introduction to Enterprise

1.1.2 Description of Fact-Finding Techniques

music business as a whole.

artist contract information, as well as album information.

information on songs and writers.

anymore, but rent independent ones for use.

what a record company does and what each artist does.

album transactions that occurred during a specific period of time.

1.1.3 Database Design Focus

as transactions for selling albums.

1.1.4 Entity and Relationship Set Description

ideas and rough drafts of our entities.

So using the information we discovered from research, we decided to focus on the

at least one member in the Member entity.

entry for Bob Dylan.

of songs, and each song will have a writer.

music information as well as some financial information.

1.2.1 Entity Set Description

Contract – Strong Entity

Our Record Company has hundreds of artists hired by

contract to the record company, with each of these contracts

ended. So a Contract entity was required.

Primary Key: contract_ID

 album_term: Number of albums artist has to record for their contract

o int; nulls not allowed; not unique; single-valued and simple.

 start_date: Date artist signed contract

o date; no nulls; not unique; single-valued and simple.

 end_date: Date artist contract ended

o date; nulls allowed; not unique; single-valued and simple.

Our Record Company has hundreds of artists hired by

contract to the record company. So we needed an Artist Entity

to represent these artists in our database.

Primary Key: artist_ID

Attributes: Figure 2- Artist Entity

 artist_ID: Internal ID for artist information

 artist_name : Name used by artist

 genre: Style of music classification

reason, a Member Entity is required.

Primary Key: ssn

 fname : First name

 lname: Last name

o string with any value; no nulls; not unique.

 phone: Phone number

 addr: Street address

o string of length 40 chars; nulls not allowed; not unique.

 city: Specific city in U.S. State

o string of length 40 chars; nulls not allowed; not unique.

 state: State located in the U.S.

 zip: Valid U.S. Zip Code

o string: nulls not allowed; not unique; single-valued and simple.

 instrument: Instrument that member plays

o string; null not allowed; not unique; single-valued and simple.

 start_date: date member’s contract began

 end_date: date member’s contract ended

Each Artist will have a certain amount of Albums. So an

Albums entity is required in order to store general information for