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