Lab 7

In this lab you will be transforming a set of tables so that they are consistent with 3NF (third normal form) and have of primary keys. There is a full set of data associated with each table. You need not transform all of the data. Rather, the goal here is to transform the table design and transform one or two rows of data from each table. That is, show a sample of what the fully transformed data would look like.

Links to each data file

The data in the tables below are of attractions and major geographical features for a bunch of countries. The individual tables are:

UML-ish for the current tables

Mountain Ranges

Attribute Name	Type	Other
name of range	varchar	primary key
countries list	varchar	foreign key to countries
peak name	varchar
peak height	int

Country

Attribute Name	Type	Other
name of country	varchar	primary key
mountain ranges list	varchar	foreign key to mountain ranges
cities list	varchar	foreign key to Destination / City

Attractions

Attribute Name	Type	Other
name of attraction	varchar	primary key
Year	int
AD	boolean
City	varchar	foreign key to Destination / City

Destinations / City

Attribute Name	Type	Other
Name of city	varchar	primary key
Country	varchar	foreign key to country
Attractions	varchar	foreign key to attractions
distance	int

Seas

Attribute Name	Type	Other
name	varchar	primary key
Country list	varchar	foreign key to country
max depth	int

Acknowledgement

This is based on a data set collected by Anna Goncharova a 2023 BMC graduate. Thanks you.

What to hand in

Send email to gtowell@brynmawr.edu with a redesigned set of tables and a couple of rows of sample data for each table.