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