Database work
Database Design & Tools • Use the start script posted on Canvas to create, populate, and add foreign key constraints
the tables provided below.
Database Description
Part 1: Primary Keys and Indexes
When creating the PARENT table, you realized that you did not include any primary key
specification. Since Mother_ID and Child_ID are all needed to fully define each row, create a
primary key that uses both of these attributes.
As you keep adding more households to your database, you’ll want to be able to query the
HOUSEHOLD table and have results displayed as fast as possible. Since you’ll be doing most of
your queries on the city, create an index on the city and state attributes.
Part 2: Auto-increment
Adding new rows to a table can sometimes be a tedious task, especially if you have to constantly
remember which primary key value needs to be inputted next. To let Oracle do the work of
autoincrementing, create a sequence for the Pet_ID.
Once the new sequence is made, add in the Freeman family’s new dog, Balto. He was born in
2021, has long fur, and lives in the same household as Nelson and Jessica Freeman.
Part 3: Views
Suppose that some of your family members want to get data about other households you keep in
your database. However, you don’t want to reveal the entire database, so you decide to create a
view to supplement this request. Create a view named VHOUSEHOLD that shows the
household’s House_ID, city, state, and zip code.
Now that you have this view created, query the view for every household in Cincinnati.
Part 4: SQL
1. Select all of the unique states that you have family living in.
2. Select the Member_IDs, names, birthdays, and cell phone numbers of everyone who lives
with
the potbelly pig, Jingles.
3. Select the address_num, street, city, and state of the first four households listed when
ordered by city.
4. Select the names of all the members and pets that live at 321 High St.
5. Select everyone who lives in a house without any pets.
Deliverables:
Your work for Parts 1, 2, 3, and 4 organized in a notepad file showing the changes made to the
Oracle database and the proper entries made.
2 | P a g e
3 | P a g e
The initial database:
MEMBER
Member_ID Name Phone_Num Birthday Gender Lives_In
1 Nelson Freeman 614-601-5147 1-Jun-1922 M 1
2 Samuel Hunter 513-395-7045 26-Aug-1922 M 4
3 William Mack 330-949-2522 12-Aug-1923 M 2
4 Mary Burbank 330-512-9725 15-Feb-1922 F 2
5 Samuel Freeman 15-Mar-1998 M 6
6 Jessica Freeman 614-719-9213 22-Jul-1952 F 1
7 Enoch Whitman 216 -848-9837 16-Dec-1918 M 3
8 Rebecca Gardner 25-May-1923 F 3
9 Jane Hunter 216-596-6321 29-Jan-1948 F 3
10 Lucerna Harlow 513-786-2650 3-Sep-1918 F 4
11 David Freeman 513-545-8812 28-Nov-1948 M 5
12 Desiah Mack 330-802-5776 14-Oct-1947 F 5
13 Nathanial Whitman 216-596-6123 17-Jun-1943 M 3
14 Joseph Freeman 312-893-2254 27-Dec-1972 M 6
15 Abigale Whitman 773-254-8919 3-Feb-1968 F 6
16 Mary Mader 614-624-6628 12-Jan-1918 F 1
17 Alex Whitman 216-596-6240 23-Jan-1948 F 3
PARENT
PET
Pet_ID PName Type Fur Type Year_born House_In
1 Jack Dog Long Hair 2008 1
2 Crookshanks Cat Long Hair 2015 1
3 Whiskers Dog Short Hair 2010 2
4 Jingles Potbelly Pig Short Hair 2004 3
Mother_ID Father_ID Child_ID
16 2 11
16 2 6
4 3 12
8 7 17
8 7 13
7 8 9
11 12 14
9 13 15
15 14 5
4 | P a g e
5 Tweet Canary Feathers 2018 5
HOUSEHOLD
House_ID Address_Num Street City State Zip
1 321 High St. Columbus OH 43050
2 123 2nd Ave. Akron OH 44223
3 444 Main Dr. Cleveland OH 44101
4 999 Freeland St. Cincinnati OH 45220
5 265 Iota Ave. Cincinnati OH 45220
6 102 Michigan Ave. Chicago IL 60632