Draw data models for the following situations.
1a.
Each computer in a company has many software packages and each software package might be installed on many computers.
1b.
A person can belong to many clubs and each club has many members.
1c.
A country can have many official languages and a language can be the official language in many countries. Each language belongs to a language group.
Write SQL statements to create the tables corresponding to the data models in questions 1 a, b, and c.
2a
CREATE TABLE COMPUTER
(COMPID CHAR(5) NOT NULL,
OPERATINGSYS CHAR(20),
CPU CHAR(10),
HARDDISK INTEGER,
RAM INTEGER,
PRIMARY KEY(COMPID))
CREATE TABLE SOFTWARE
(SWID CHAR(5) NOT NULL,
SWNAME CHAR(20),
SWEDITION CHAR(4),
PRIMARY KEY (SWID))
CREATE TABLE INSTALLED
(INSTALLDATE DATE
SWID CHAR(5) NOT NULL,
COMPID CHAR(5) NOT NULL,
PRIMARY KEY (SWID, COMPID),
FOREIGN KEY FKCOMPUTER(COMPID) REFERENCES COMPUTER,
FOREIGN KEY FKSOFTWARE(SWID) REFERENCES SOFTWARE)
2b.
CREATE TABLE PERSON
(PERSID CHAR(6) NOT NULL,
PERSFNAME CHAR(15),
PERSONAME CHAR(15),
PERSLNAME CHAR(15),
PRIMARY KEY (PERSID))
CREATE TABLE CLUB
(CLUBNAME CHAR(15) NOT NULL,
CLUBPURPOSE CHAR(20),
CLUBSTREET CHAR(15),
CLUBCITY CHAR(15),
PRIMARY KEY (CLUBNAME))
CREATE TABLE MEMBER
(JOINDATE DATE,
CLUBNAME CHAR(15) NOT NULL,
PERSID CHAR(6) NOT NULL,
PRIMARY KEY (CLUBNAMEM, PERSID),
FOREIGN KEY FKCLUB (CLUBNAME) REFERENCES CLUB,
FOREIGN KEY FKPERSON (PERSID) REFERENCES PERSON)
2c.
CREATE TABLE COUNTRY
(CNAME CHAR(15) NOT NULL,
PRIMARY KEY (CNAME))
CREATE TABLE GROUP
(GROUPNAME CHAR(15) NOT NULL,
PRIMARY KEY(GROUPNAME))
CREATE TABLE LANGUAGE
(LANGUAGE CHAR(20) NOT NULL,
GROUPNAME CHAR(15),
PRIMARY KEY(LANGUAGE),
FOREIGN KEY FKGROUP (GROUPNAME) REFERENCES GROUP)
CREATE TABLE OFFICIAL
(LANGUAGE CHAR(20) NOT NULL,
CNAME CHAR(15) NOT NULL,
PRIMARY KEY(LANGUAGE, CNAME),
FOREIGN KEY FKLANGUAGE (LANGUAGE) REFERENCES LANGUAGE,
FOREIGN KEY FK COUNTRY(CNAME) REFERENCES COUNTRY)
Answer the following queries based on the described data model.
3a.
Is Spanish an official language of The Netherlands?
SELECT CNAME FROM COUNTRY, OFFICIAL, LANGUAGE WHERE COUNTRY.CNAME = OFFICIAL.CNAME AND LANGUAGE.LANGId = OFFICIAL.LANGID AND CNAME = "The Netherlands" AND LANGNAME = "Spanish"
3b.
How many countries have Japanese as their official language?
SELECT COUNT(CNAME) FROM COUNTRY, OFFICIAL, LANGUAGE WHERE COUNTRY.CNAME = OFFICIAL.CNAME AND LANGUAGE.LANGID = OFFICIAL.LANGID AND LANGUAGE = "Japanese"
3c.
What official languages are spoken in Belgium?
SELECT LANGNAME FROM COUNTRY, OFFICIAL, LANGUAGE WHERE COUNTRY.CNAME = OFFICIAL.CNAME AND LANGUAGE.LANGID = OFFICIAL.LANGID AND COUNTRY.CNAME = "Belgium"
3d.
How many different official languages are spoken in Switzerland?
SELECT COUNT(langname) FROM COUNTRY, OFFICIAL, LANGUAGE WHERE COUNTRY.CNAME = OFFICIAL.CNAME AND LANGUAGE.LANGID = OFFICIAL.LANGID AND COUNTRY.CNAME = "Switzerland"
4.
A furniture manufacturer has asked you to draw a data model for a simple database for its couch collection. Many couches can be delivered with several different fabric choices.
5.
You work for a large college placement company that helps students to decide which college best matches their needs. For each college, it tracks the college's name, the city and state in which it is located, if it is private or public, the size of the college, the average SAT score and the average ACT score, the percent of students that receive a bachelors degree from this school, the average length of time a student is at the school prior to graduation, the percent of full time students. Also the placement company wants to know which schools offer which majors. Colleges offer many majors and a major is offered at many colleges. The database also contains a brief description of the major. Draw the data model.
6.
This same recruiting firm tracks where its clients have applied and the outcome of these applications. Most clients apply to many colleges and a many students apply to the same college. The student information it tracks is the students contact information (address, phone #, email address), the students ACT and SAT scores, the students GPA, and which H.S. the student graduated from. For each college it tracks the colleges name, the city and state in which it is located, if it is private or public, the size of the college, the average SAT score and the average ACT score, the percent of students that receive a bachelors degree from this school, the average length of time a student is at the school prior to graduation, the percent of full time students. For the application they want to know when the student applied, when they heard from the college, the colleges decision to admit or not and the students decision to attend the college or not.
7.
You started a small mail order business out of your home.You make picture frames (several different sizes and types), business card holders and trivets out of Popsicle sticks.You have found it difficult to keep track of all of the orders. To manage this problem you want to create a database. You know you need to track all of your products, the products name and type, the price of it, the amount of time it takes you to make it and the cost of the materials for it. Each product can be ordered many times and an order can have many products on it.So order #12333 may have 3 'Dog house 5x7 picture frames' ' and 1 'You’re in the dog house' trivet. You also want to track your customers. The customer information you track includes contact name and information. Customer may make many orders but an order only goes to one customer. Make the data model.
8.
A wine is comprised of many grapes and a grape can be in many wines. using the data model, create the following queries.
What wines use more than two types of grapes?
select WineID from proportion group by WineID having count(*) >2
b.
What is the wine storage and wine aging of the grapes used in the wine with the highest alcohol%?
Select WineStorage, WineAging From grape, proportion, wine Where wine.wineid=proportion.wineid and Proportion.variety=grape.variety and Alcohol% = (select max(alcohol%) from wine)
c.
Do you have any wines in the database whose proportions of grapes add up to more than 100 or less then 100?
Select WineID From proportion Group by wineid Having sum(percent) <>100
d.
Find all wines that are in the cabernet category or that have a grape that was stored in oak?
Select wine.wineID From grape, proportion, wine Where wine.wineid=proportion.wineid and Proportion.variety=grape.variety and (WineStorage = ‘Oak’ or Category = ‘Cabernet’)
| This page is part of the promotional and support
material for Data Management (open edition) by Richard T. Watson For questions and comments please contact the author |