Note1: this assignment is a slight modification of material developed by the Stanford Database Group
Note2: Remember to back up your work!
In this phase of your PDA you will make sure your relations have keys and foreign keys, create a substantial amount of data for you databases, and load your database with this data using the mySQL "load" command. First, make sure that your relations have keys and that the relations created from ER Model relationships use foreign keys to specify the keys of the relations you are referencing. To create the data, write a program in any programming language you like that creates large files of records in a format acceptable to the load command, then load the data into your PDA relations. If you are using real data for your PDA, your program will need to transform the data into files of records conforming to your PDA schema. The rest of you will write a program to fabricate data: your program will generate either random or nonrandom (e.g., sequential) records conforming to your schema. It is both fine and expected for your data values--strings especially--to be meaningless gibberish. The point of generating large amounts of data is so that you can experiment with a database of realistic size, rather than the small ``toy'' databases often used in classes. The data you generate and load should be on the order of:
If the semantics of your application includes relations that are expected to be relatively small (e.g., schools within a university), it is fine to use some small relations, but please ensure that you have relations of the sizes prescribed above as well. When writing a program to fabricate data, there are two important points to keep in mind:
To help you generate your data file here is a sample java file: DataGenerator.java
load data local infile '/Users/leut/data_sailors' into table sailors fields terminated by ',' lines terminated by '\n' ; load data local infile '/Users/leut/data_boats' into table boats fields terminated by ',' lines terminated by '\n' ; load data local infile '/Users/leut/data_reserve' into table reserve fields terminated by ',' lines terminated by '\n' ;
mysql> use Sailors2 ; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables ; +--------------------+ | Tables_in_sailors2 | +--------------------+ | Boats | | Reserve | | Sailors | +--------------------+ 3 rows in set (0.00 sec) mysql> select count*[K(*) from Boats ; +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (0.01 sec) mysql> select count(*) from Reserve ; +----------+ | count(*) | +----------+ | 29999 | +----------+ 1 row in set (0.01 sec) mysql> select count(*) from Sailors ; +----------+ | count(*) | +----------+ | 3000 | +----------+ 1 row in set (0.00 sec) mysql> describe Boats ; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | bid | int(11) | NO | PRI | 0 | | | name | varchar(20) | YES | | NULL | | | ratingNeeded | int(11) | YES | | NULL | | | bcolor | varchar(20) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> describe Sailors ; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | sid | int(11) | NO | PRI | 0 | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | | rating | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> describe Reserve ; +-------+---------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+------------+-------+ | bid | int(11) | NO | PRI | 0 | | | sid | int(11) | NO | PRI | 0 | | | rdate | date | NO | PRI | 0000-00-00 | | +-------+---------+------+-----+------------+-------+ 3 rows in set (0.01 sec) mysql> select * from sailors where sid < 5 ; +-----+------+------+--------+ | sid | name | age | rating | +-----+------+------+--------+ | 1 | Bob1 | 9 | 4 | | 2 | Bob2 | 8 | 1 | | 3 | Bob3 | 2 | 2 | | 4 | Bob4 | 2 | 7 | +-----+------+------+--------+ 4 rows in set (0.01 sec) mysql> select * from boats where bid < 6 ; +-----+--------+--------------+--------+ | bid | name | ratingNeeded | bcolor | +-----+--------+--------------+--------+ | 1 | guppy1 | 3 | red1 | | 2 | guppy2 | 10 | red2 | | 3 | guppy3 | 3 | red3 | | 4 | guppy4 | 4 | red4 | | 5 | guppy5 | 4 | red5 | +-----+--------+--------------+--------+ 5 rows in set (0.00 sec) mysql> select * from reserve where sid = 1 ; +-----+-----+------------+ | bid | sid | rdate | +-----+-----+------------+ | 53 | 1 | 2013-11-26 | | 104 | 1 | 2013-03-26 | | 356 | 1 | 2013-03-06 | | 400 | 1 | 2013-01-03 | | 437 | 1 | 2013-04-16 | | 507 | 1 | 2013-12-27 | | 678 | 1 | 2013-10-02 | | 787 | 1 | 2013-07-08 | | 828 | 1 | 2013-02-15 | | 985 | 1 | 2013-12-15 | +-----+-----+------------+ 10 rows in set (0.01 sec) mysql> quit ;