Refine your database and SQL statements. Verify that the database completely fol

Question

Refine your database and SQL statements.

Verify that the database completely follows the business logic and design you created in the previous weeks.

Create queries using your new database to demonstrate that the data is useful and meaningful.

Submit the final database and database scripts to the instructor for testing and installation.

Insert values Table Student:

INSERT INTO Student VALUES (1, ‘001’, ‘Joseph’, ‘5555552960’, ‘1985-03-04’, ‘123’);

INSERT INTO Student VALUES (2, ‘002’, ‘Cynthia’, ‘5555552970’, ‘1990-01-23’, ‘124’);

INSERT INTO Student VALUES (3, ‘003’, ‘Jonah’, ‘5555552980’, ‘1996-02-28’, ‘125’);

INSERT INTO Student VALUES (4, ‘004’, ‘Elizabeth, ‘5555552990’, ‘1998-06-23’, ‘126’);

INSERT INTO Student VALUES (5, ‘005’, ‘Josh’, ‘5555553000’, ‘1988-07-01’, ‘127’);

Insert values Table Course:

INSERT INTO Course VALUES (1, ‘123’, ‘ITS’, ‘111’, ‘4’, ‘120’);

INSERT INTO Course VALUES (2, ‘124’, ‘Eng’, ‘222’, ‘4’, ‘120’);

INSERT INTO Course VALUES (3, ‘125’, ‘SCI’, ‘333’, ‘4’, ‘120’);

INSERT INTO Course VALUES (4, ‘126’, “HIST’, ‘444’, ‘4’, ‘120’);

INSERT INTO Course VALUES (5, ‘127’, ‘POLYSCI’, ‘555’, ‘4’, ‘120’);

Insert values Table Faculty:

INSERT INTO Faculty VALUES (1, ‘111’, Voorhies’, 123, ‘4444448976’);

INSERT INTO Faculty VALUES (2, ‘222’, Williams’, 124, ‘4444447867’);

INSERT INTO Faculty VALUES (3, ‘333’, Myers’, ‘125’, ‘4444449089’);

INSERT INTO Faculty VALUES (4, ‘444’, Kruger’, ‘126’, ‘4444449098);

INSERT INTO Faculty VALUES (5, ‘555’, ‘Sawyer’, ‘127’, 4444446235);

Insert values Table Scholarship:

INSERT INTO Scholarship VALUES (1, ‘1234’, ‘bigpay’, ‘$2,000.00’, 123);

INSERT INTO Scholarship VALUES (2, ‘1235’, ‘paybig’, ‘$2,000.00’, 124);

INSERT INTO Scholarship VALUES (3, ‘1236’, ‘gofree’, ‘$2,000.00’, 125);

INSERT INTO Scholarship VALUES (4, ‘1237’, ‘payoff’, ‘$2,000.00’, 126);

INSERT INTO Scholarship VALUES (5, 1238’, ‘zeroaccount’, $2,000.00’, 127);

Insert values Table DisciplinaryAction:

INSERT INTO DisciplinaryAction VALUES (1, ‘567’, ‘suspended’, ‘2 weeks’, ‘001’);

INSERT INTO DisciplinaryAction VALUES (2, ‘568’, ‘expelled’, ‘1 year’, ‘002’);

INSERT INTO DisciplinaryAction VALUES (3, ‘569’, ‘written-up’, ‘0’, ‘003’);

INSERT INTO DisciplinaryAction VALUES (4, ‘570’, ‘warning’, ‘0’, ‘004’);

INSERT INTO DisciplinaryAction VALUES (5, ‘571’, ‘in-school suspension’, ‘1 week’, ‘005’);

Explanation / Answer

The term functional dependency comes up less often than the ones previously mentioned, but we will need to understand it to understand the normalization process that we will discuss later. If there is a functional dependency between column A and column B in a given table, which may be written A —> B, then the value of column A determines the value of column B. For example, in the employee table, the employeeID functionally determines the name (and all the other attributes in this particular example). Schemas The term schema or database schema simply means the structure or design of the database—that is, the form of the database without any data in it. If you like, the schema is a blueprint for the data in the database. We can describe the schema for a single table in the following way: Table: employee employeeID name job departmentID

Database Design Principles Planning Think Ahead!! Perhaps the single most important thing to do when you start designing a database is to think ahead. Before you even switch on the computer, think about the type of information you have to work with and the types of questions you’ll want your database to answer. To rephrase: What information needs to be stored or what things or entities do we need to store information about? And what questions will we need to ask of the database? When thinking about these questions, we must bear in mind the rules of what we are trying to model — what the things are that we need to store data about and what specifically are the links between them. A typical design cycle is identify data->set data types->normalize tables/assign keys->rinse/repeat Let’s revisit the white pages example presented earlier. It should be easy: all you need is the name, the address and the phone number of each person. Your initial table design thus consists of three fields: name, address and phone number. Right? That gives us the following structures.

Name
Address
Phone Number
That seems clear enough. But we need the names sorted alphabetically by last name. There’s no
easy way to do it, so it makes sense to break the Name into its component parts: First Name, Middle
Initial, Last Name.
The same goes for the address. It, too, can be usefully broken down into Street Address, City, State
and Postal Code.
The best way to see if the database design works is to test it with some sample data, so feed the
following records into your hypothetical table:
NAME ADDRESS PHONE NUMBER
Jay T. Apples 100 Megalong Dr Etna 4992122
Beth York 2/53 Alice Lebanon 5050011
Mike. R. Sullivan 9 Jay Lebanon 4893892
Barry J. Anderson 71 Wally Rd Hanover 2298310
Now tell the database program to sort the information:
NAME ADDRESS PHONE NUMBER
Barry J. Anderson 71 Wally Rd Hanover 2298310
Beth York 2/53 Alice Lebanon 5050011
Jay T. Apples 100 Megalong Dr Etna 4992122
Mike. R. Sullivan 9 Jay Lebanon 4893892

Immediately, you can see this is not what you want. You want the table sorted alphabetically by last
name, not first name.
How can you fix this? Well, you could do some complex manipulation using a database feature called
‘string functions’ – if your program supports such a feature. Or, you could come up with a better table
design in the first place: last name, middle initial(s), First name, address and phone number. Feed
your test data into this revised structure then tell the database program to sort your table using the
last name followed by the initials.
Now our table structure is:
LastName
FirstName
Mid
Address
Phone
This time, you’ll get the correct alphabetical listing:
LastName FirstName Mid Address Phone
Anderson Barry J. 71 Wally Rd Hanover 2298310
Apples Jay T. 100 Megalong Dr Etna 4992122
Sullivan Mike. R. 9 Jay Lebanon 4893892
York Beth 2/53 Alice Lebanon 5050011
Don’t stop there. The table can be even more effective if you break the structure down further. For
instance, if you’d like to have an easy way to list only those people who live in Leichhardt, this design
won’t help you. But with a little more work, you can break your database structure down further into

first name, last name, middle initials, street address, city, and phone number.
Now our table structure is:
FirstName
Mid
LastName
Street
City
Phone
LastName FirstName Mid Street City Phone
Anderson Barry J. 71 Wally Rd Hanover 2298310
Apples Jay T. 100 Megalong Dr Etna 4992122
Sullivan Mike R. 9 Jay Lebanon 4893892
York Beth 2/53 Alice Lebanon 5050011

Multi-table flexibility
On the other hand, if you use a multi-table relational database, you can store the CD details (name,
date, tracks and so on) in a CD table and store the artist details once in an Artist table. Your CD table
will look like this:
cd_name
cd_date
genre
tracks
artist_or_band_name
Your Artist table will look like this:
artist_or_band_name
band_members
recording_label
discography
notes
You then link the two tables using the artist/band name field (that’s why it’s called a relational
database – you define relationships between the tables) as the Foreign Key and enter the artist
information once only. Each time you add a subsequent Beatles CD to your collection, you type The
Beatles in the artist field and the database looks up the other details for you. It not only minimizes
effort on your part, it also ensures consistency of information and minimizes the chance of introducing
errors into the data.
To further flexibility you could continue and create a songs table:
cd_name
song_title
duration
track_number
writer
vocals
and link it to the CD by using CD Name as the Foreign Key. Better yet would be to use a CD_ID as
the primary key of the CD table and use CD ID as the Foreign Key in hte songs table. This is
because you know, as an avid collector of covers, that CDs may share same name.
You can now get a list of all your CDs that contain a given song - great for us collectors of covers!

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.