Databasteknik: The "demo" database

These are the tables and the data in the demo database, used in some of the lab assignments. A file with SQL commands that will create the demo database tables, and fill them with these example data, is available as create-demo-database.txt. This is just the same information, presented in a more human-readable way.

SQL data definition statements

create table supplier (
	number integer,
	name varchar(15),
	city varchar(15),
	state varchar(6));

create table store (
	number integer,
	city varchar(15),
	state varchar(6));

create table employee (
	number integer,
	name varchar(20),
	salary integer,
	manager integer,
	birthdate integer,
	startdate integer);

create table dept (
	number integer,
	name varchar(20),
	store integer,
	floor integer,
	manager integer);

create table item (
	number integer,
	name varchar(20),
	dept integer,
	price integer,
	qoh integer,
	supplier integer);

create table sale (
	number integer,
	date varchar(8),
	store integer,
	dept integer,
	item integer,
	quantity integer,
	employee integer,
	credit varchar(8));

create table parts (
	pnum integer,
	pname varchar(20),
	color varchar(8),
	weight integer,
	qoh integer);

create table supply (
	snum integer,
	pnum integer,
	jnum integer,
	shipdate varchar(8),
	quan integer);

Content of the table "supplier"

number	name	city	state
199	Koret	Los Angeles	Calif
213	Cannon	Atlanta	Ga
33	Levi-Strauss	San Francisco	Calif
89	Fisher-Price	Boston	Mass
125	Playskool	Dallas	Tex
42	Whitman's	Denver	Colo
15	White Stag	White Plains	Neb

Content of the table "store"

number	city	state
5	San Francisco	Calif
7	Oakland	Calif
8	El Cerrito	Calif

Content of the table "employee"

number	name	salary	manager	birthdate	startdate
157	Jones, Tim	12000	199	1940	1960
1110	Smith, Paul	6000	33	1952	1973
35	Evans, Michael	5000	32	1952	1974
129	Thomas, Tom	10000	199	1941	1962
13	Edwards, Peter	9000	199	1928	1958
215	Collins, Joanne	7000	10	1950	1971
55	James, Mary	12000	199	1920	1969
26	Thompson, Bob	13000	199	1930	1970
98	Williams, Judy	9000	199	1935	1969
32	Smythe, Carol	9050	199	1929	1967
33	Hayes, Evelyn	10100	199	1931	1963
199	Bullock, J.D.	27000	0	1920	1920
4901	Bailey, Chas M.	8377	32	1956	1975
843	Schmidt, Herman	11204	26	1936	1956
2398	Wallace, Maggie J.	7880	26	1940	1959
1639	Choy, Wanda	11160	55	1947	1970
5119	Ferro, Tony	13621	55	1939	1963
37	Raveen, Lemont	11985	26	1950	1974
5219	Williams, Bruce	13374	33	1944	1959
1523	Zugnoni, Arthur A.	19868	129	1928	1949
430	Brunet, Paul C.	17674	129	1938	1959
994	Iwano, Masahiro	15641	129	1944	1970
1330	Onstad, Richard	8779	13	1952	1971
10	Ross, Stanley	15908	199	1927	1945
11	Ross, Stuart	12067	0	1931	1932

Content of the table "dept"

number	name	store	floor	manager
35	Book	5	1	55
10	Candy	5	1	13
19	Furniture	7	4	26
20	Major Appliances	7	4	26
14	Jewelry	8	1	33
43	Children's	8	2	32
65	Junior's	7	3	17
58	Men's	7	2	129
60	Sportswear	5	1	10
99	Giftwrap	5	1	98
1	Bargain	5	0	7
26	Linens	7	3	157
63	Women's	7	3	38
49	Toys	8	2	35
70	Women's	5	1	10
73	Children's	5	1	10
34	Stationary	5	1	33
47	Junior Miss	7	2	129
28	Women's	8	2	32

Content of the table "item"

number	name	dept	price	qoh	supplier
26	Earrings	14	1000	20	199
118	Towels, Bath	26	250	1000	213
43	Maze	49	325	200	89
106	Clock Book	49	198	150	125
23	1 lb Box	10	215	100	42
52	Jacket	60	3295	300	15
165	Jean	65	825	500	33
258	Shirt	58	650	1200	33
120	Twin Sheet	26	800	750	213
301	Boy's Jean Suit	43	1250	500	33
121	Queen Sheet	26	1375	600	213
101	Slacks	63	1600	325	15
115	Gold Ring	14	4995	10	199
25	2 lb Box, Mix	10	450	75	42
119	Squeeze Ball	49	250	400	89
11	Wash Cloth	1	75	575	213
19	Bellbottoms	43	450	600	33
21	ABC Blocks	1	198	405	125
107	The 'Feel' Book	35	225	225	89
121	Ski Jumpsuit	65	4350	125	15

Content of the table "sale"

number	date	store	dept	item	quantity	employee	credit
100581	75-01-15	7	26	118	5	157	00000000
100581	75-01-15	7	26	120	1	157	00000000
100582	75-01-15	8	14	26	1	1110	24356540
100586	75-01-16	8	43	127	3	35	54096831
100586	75-01-16	8	49	106	2	35	54096831
100592	75-01-17	7	58	258	1	129	00000000
100593	75-01-18	5	10	23	2	13	11652133
100594	75-01-18	5	60	52	1	215	12591815

Content of the table "parts"

pnum	pname	color	weight	qoh
1	central processor	pink	10	1
2	memory	gray	20	32
3	disk drive	black	685	2
4	tape drive	black	450	4
5	tapes	gray	1	250
6	line printer	yellow	578	3
7	l-p paper	white	15	95
8	terminals	blue	19	15
13	paper tape reader	black	107	0
14	paper tape punch	black	147	0
9	terminal paper	white	2	350
10	byte-soap	clear	0	143
11	card reader	gray	327	0
12	card punch	gray	427	0

Content of the table "supply"

snum	pnum	jnum	shipdate	quan
475	1	1001	73-12-31	1
475	2	1002	74-05-31	32
475	3	1001	73-12-31	2
475	4	1002	74-05-31	1
122	7	1003	75-02-01	144
122	7	1004	75-02-01	48
122	9	1004	75-02-01	144
440	6	1001	74-10-10	2
241	4	1001	73-12-31	1
62	3	1002	74-06-18	3
475	2	1001	73-12-31	32
475	1	1002	74-07-01	1
5	4	1003	74-11-15	3
5	4	1004	75-01-22	6
20	5	1001	75-01-10	20
20	5	1002	75-01-10	75
241	1	1005	75-06-01	1
241	2	1005	75-06-01	32
241	3	1005	75-06-01	1
67	4	1005	75-07-01	1
999	10	1006	76-01-01	144
241	8	1005	75-07-01	1
241	9	1005	75-07-01	144

Thomas Padron-McCarthy (Thomas.Padron-McCarthy@oru.se), October 31, 2007