CS320 Project

Project part 3: Implementing Schema and Collecting Data

100 points

Step 3 of Your DB

If you have modified your design because of feedbacks from the instructor (or any other reason), please explain the reasons in your report.

1. Write an SQL database schema for your DB, and input into the PostgreSQL database that has been created for your team. Use the CREATE TABLE commands described in class.
   • Pick suitable datatypes for each attribute following the principal options regarding types in the textbook. 
   • Choose and declare primary keys and (if appropriate) other unique attributes or sets of attributes.
   • Declare the necessary foreign keys.

   Hand in a file with the commands you use to create your database schema. Show the response of PostgreSQL to a request to describe each of your relation schemas. For example, to see the schema for relation Foo type

    \d(Foo);
   

2. Gather a significant volume of data for your database and bulk load it into your relations. You should use real data for your DB as much as possible. Write a program in any programming language you like to collect and process the data, then load the data into your DB relations. Your program will need to transform the data into files of records conforming to your DB schema. If certain real data is not available, explain the reasons and write a program to fabricate a large amount of data: Your software will produce records that are either random or structured (for instance, sequential) in accordance with your schema. It's crucial that the data you create resembles genuine data. The aim of producing vast quantities of data is to allow experimentation with a realistically-sized database, as opposed to a small-scale or "toy" database. 

   When writing a program to fabricate data, there are two important points to keep in mind:

   • Do not to generate duplicate values for keys of your relations or for other (sets of) unique attributes.
   • For relations that are expected to join with each other. For example, a Student relation with attribute courseNo is expected to join with attribute number in relation Course. When generating data for these two relations, make sure that the values generated can actually join--otherwise all of your queries will generate empty results! To ensure join compatibility, you can first produce values in one relation and then utilize those values to determine the joining values in another relation. For instance, you might initially create number in relation Course first and then use these numbers to populate the courseNo entries in the student relation.

   Turn in your program code for generating or transforming data, a small sample of the records generated for each relation (5 or so records per relation), and a script showing the loading of your data into the database.