A homework assignment designed to help practice creating relational database tables in SQL using a SQLite database.
The homework.db
SQLite database is an empty database--there are no tables in here whatsoever. Your job is to create the tables for this database, and populate each table with data. Again, as with all the homework, the goal is not merely to achieve the creation of the table--the goal is to practice writing the SQL to create the tables. Thus, as with the other assignments, you must do all of the work (creating the tables as well as populating them) in SQL statements stored in script files.
NOTE: By putting all the work into individual script files, it will make it easier to develop the database piecemeal--write one script, run it against your database, then you can drop the database entirely and start over with the second script. This can be useful when debugging typos and other things in your scripts: for example, it's common that you run the script, there's an error with one of your INSERT statements (but the rest executed correctly), so you fix the error, run the script again, and now all your other INSERT statements fail (because you're trying to INSERT duplicate primary key values), and so does the CREATE TABLE (because the table already exists from the previous run)! This can be frustrating, so by doing it piecemeal, you can delete everything and start from scratch.
CREATE statements can also be qualified with IF NOT EXISTS
(such as CREATE TABLE IF NOT EXISTS tablename ...
), which tells the database to skip the CREATE statement if the table already exists. Alternatively, you can write DROP TABLE tablename
right in front of your CREATE TABLE tablename...
statement, and SQLite will drop any existing table (such as from a previous run) before creating the table; this is a common trick many SQL professionals and tools use, particularly since DROP TABLE
also deletes any data stored in that table.
You need to create five tables in the database, with varying features in each, and load some data into each table. Each one should be in its own file, whose filename is given in the title of each section (so the Students section should be in the file students.sql
.)
Note that while you could create each of these tables as being entirely composed of "TEXT" fields, you lose the ability to impose any integrity constraints--make sure the data types you use are the most restrictive types possible for that kind of data. For example, an "age" field could be a TEXT, but it would be more restrictive to be an INTEGER field instead.
Create a table called "students" that has the following attributes on it:
id
, which needs to be an integer primary keyfirstname
, which needs to be a string type limited to 40 characterslastname
, which needs to be a string type limited to 80 charactersage
, which needs to be a numeric type
Insert the following data:
id | firstname | lastname | age -- + --------- + -------- + ---- 1 | Fred | Flintstone | 35 2 | Wilma | Flintstone | 29 3 | Barney | Rubble | 33 4 | Betty | Rubble | 29 5 | Pebbles | Flintstone | 1 6 | Bam-Bam | Rubble | 1
Add a few additional rows with names of your own. (Learning how to make up credible fake data for your database is half the fun--and the pain--of working with databases!)
Create a table called "courses" that has the following attributes on it:
code
, which needs to be a string limited to 40 characters and is a primary keydescription
, which needs to be a string limited to 400 charactersstart
, which needs to be a dateend
, which needs to be a date
In addition, make sure the following constraints are enforced:
code
can never be NULL and must be at least 7 characters longstart
andend
can never be NULL- the value of
end
for a given row must always be afterstart
Insert the following data:
code | start | end | description |
---|---|---|---|
INFO330A | 1 April 2023 | 1 June 2023 | Data and databases |
INFO314 | 1 April 2023 | 1 June 2023 | Networking and distributed Systems |
INFO448A | 25 September 2023 | 19 December 2023 | Introduction to iOS |
INFO449A | 25 September 2023 | 19 Dceember 2023 | Introduction to Android |
BAW010 | 1 April 2023 | 1 June 2023 | Introduction to Basket-Weaving |
BAW100A | 1 April 2023 | 1 June 2023 | Underwater Basket-Weaving |
Again, remember, the table types used must be the most restrictive possible, so be sure to use native SQL types for the start
and end
columns.
Create a table called "buildings" that has the following attributes to it:
name
, the full name of the building, which is a string of 80 charactersshortname
, a shortened reference to the building name, which is a string of 10 charactersid
, an integer primary key that is auto-generated by the database (that is to say, you do not specify the id when you insert values into the table)
In addition, make sure the following constraints are enforced:
name
can never be NULLshortname
must always be unique
Buildings also have rooms, so create a second table, called "rooms" that has the following:
number
, an integer identifier for the roombuildingid
, an integer referring to thebuildings
primary keyseating
, an integer reflecting the number of open seats in the room
NOTE: Inserting into rooms can be tricky in a script if you don't know the auto-generated IDs for the building ahead of time, because the database is responsible for generating them. This is where a nested select can often be helpful: INSERT INTO rooms VALUES (400, (SELECT id FROM buildings WHERE name="Mary Gates Hall"), 40);
for example.
Insert the following data:
- Mary Gates Hall (shortname "MGH"), which has the following rooms in it:
- Rm 400, which seats 40
- Rm 500, which seats 40
- Rm 100, which seats 5
- Raitt Hall (shortname "RAI"), which has the following rooms in it:
- Rm 123, which seats 70
- Rm 456, which seats 5
- Rm 457, which seats 5
- Meany Hall (shortname "MEA"), which has the following rooms in it:
- Rm 1, which seats 400
Students need to be able to sign up for courses, which is a many-to-many relationship, so we need to create a table that reflects this relationship. Create a table called "student_courses" that has the following attributes:
studentid
, a foreign key to the student taking the coursecourse
, a foreign key to the course they are takinggrade
, a floating-point value that defaults to NULL when data is inserted
Make sure the following students are enrolled in the following list of courses:
- Fred Flintstone is taking INFO330A, INFO448A, and INFO314
- Barney Rubble is taking INFO330A, and INFO449A
- Wilma Flintstone is taking BAW010 and BAW100
- Betty Rubble is taking BAW010
- Each table is created with the appropriate data type for column, and has the required constraints established on it. (1 pt)
- Put courses in rooms at times. (2 pts)
- Create a new table,
timeslots
, that contain the start and end times that courses can be held and anid
primary key. - Modify
room
to have aid
primary key. - Create another table,
schedule
, that has acoursecode
, aroomid
, and atimeslotid
, and add an integrity constraint that requires the combination ofroomid
, andtimeslotid
to be unique--in other words, you can never have two different courses in the same room at the same time!
- Create a new table,