Lab2-Constraints
.odt
School
Longwood University**We aren't endorsed by this school
Course
CMSC 362
Subject
Computer Science
Date
Dec 17, 2024
Pages
4
Uploaded by ConstableTeam26556
Lab 2 : Implementing "This database class @#$&@!" (and other useless rants)CMSC 362MarmorsteinSpring 2023Due Thursday (2/16/2023) by 11:59pmIn class, we’ve spent the last two weeks learning how to represent database queries and constraints using algebraic structures. This important theoretical work is abstract, but it allows us to think about and write much better queries. In addition to the theoretical material, there are also many more practical aspects of SQL that are important to know. It’s time to return a bit to the practical side and start building some databases using SQL.The purpose of this lab is to reinforce the commands we learned in Lab 1 for building tables in SQL, to introduce SQL constraints, and to practice using SQL to build tables. We will do this by implementing a simple "rant" database which could serve as the back end to a blog or other web site.Step 1. Setting UpWe are going to again use the Postgresql database server we used in the previous lab. Remember, to log in you can use psql:psql -h postgres -U lastnameYou can upload a file to the PostgreSQL interpreter using the -f flag:psql -h postgres -U lastname -f filename.sqlCreate a folder named Lab2. In that folder create a file named "lab2.sql". All of your sql code will go in this file. At the top of your file type two dashes followed by a space and then your name, thetext "CMSC 362" and the words "Lab 2". In this lab, I am going to introduce several new concepts, but you will need to figure out how to apply them for yourself. You may find the PostgreSQL documentation here helpful:http://www.postgresql.org/docs/current/static/Step 2. Some New TypesLast time we used the INTEGER, CHAR, VARCHAR, and a few other simple types. Postgres supports many other powerful types. Here are some of the interesting ones:NUMERIC : stores fractional/floating point values without rounding (up to the maximum precision of the system).BIGINT : stores a 64-bit signed integer.SERIAL: alias for “INTEGER” that also creates a sequence. A sequence is a bit like a counter variable. Every time you insert a record into the table, the “SERIAL” column will automatically be incremented with each new record.TEXT: stores a block of text of arbitrary length.BYTEA: stores binary data such as an image (BYTEA stands for “Byte Array”).BLOB: similar to BYTEA, but part of the SQL Standard (Blob stands for "Binary Large Object".
BOOLEAN: stores a single true or false valuePOINT: stores an (x,y) pairBOX: stores a pair of points (representing corners of a box)In your lab2.sql file, add SQL code to perform the following operations: Create a table named "Rants". Your table should have the fields "username", "rant", "post_id" (which should be a SERIAL) and "time_posted" (which should be a timestamp). Use appropriate types selected from those in the list above and/or Lab1. Add a comment explaining why that type is appropriate. SQL comments start with two dashes: --Create another table named "Accounts". It should have three fields "username", "password", and"image" (which should be a BYTEA).Create a third table named "Comments" which has the fields "username", "post_id", "comment_id", and "comment_text".Step 3. Dropping TablesThe best way to drop a table is to use the DROP TABLE IF EXISTS command:DROP TABLE IF EXISTS <tableName>;In a real production system, we would almost never want to drop tables directly (because doing soloses all their data). Instead, we would either use ALTER TABLE statements to modify the schema or (even better) use migrations in our high-level code to automatically generate and apply schema-altering statements. In this lab, however, dropping and recreating them is probably the easiest way to fix mistakes in your lab – it will also demonstrate that you understand some of the constraint logic.At the top of your lab2.sql file add SQL code to drop all three of your new tables if they already exist. Save your work and test, by running the .sql file with psql, that all three tables can be dropped and recreated.Step 4. ConstraintsIn class, we’ve talked about ways to add constraints to a database.Modify the code in your lab2.sql file to add the following constraints:1. post_id should be the primary key of the Rants table.2. username should be the primary key of the Accounts table.3. The primary key of the Comments table should be the pair of attributes (post_id, comment_id).4. The “post_id” field of the Comments table should be a Foreign Key referencing the post_id field in Rants.5. The “username” fields in Rants and Comments should both be Foreign Keys referencing the username field in Accounts.6. The “rant” field in Rants should never be NULL.
Step 5. Calling SQL functionsLike most modern programming languages, SQL allows us to create and use functions. We will worry about creating our own functions (called “stored procedures”) later in the course. For today, we are just going to use some built-in functions that SQL already provides for us. In particular, we are going to use the "md5" function. This function takes a string of text and uses the MD5 cryptographic hash function to create a digest. This is useful, because we don't really want to store password in plain text. Even in the database, they would be vulnerable if our systemis compromised. Unfortunately, md5 isn’t considered a very secure hash any more, but adding support for more sophisticated ciphers such as SHA-512 or blake3 in Postgres requires setting up the pgcrypto library, which is complicated. So we’ll use the simpler md5 function for this lab.To create an account in our rant database, we will use the INSERT INTO command you already know and have already used in Lab 1.To insert the username "frank" with encrypted password "dumbPassword", you would typeINSERT INTO accounts (username, password) VALUES ('frank', md5('dumbPassword') );Add this command to the bottom of your lab2.sql file now. Then add the following accounts (listed as username: password):tom: fish1harry: fish2celeste: fish3Step 6. Converting Schemas to SQLNow, at the bottom of your lab2.psql file, add SQL code to create tables for the following relation schemas:Artist(name: string, age: integer)Song(title: string, year: integer, composer: string)Performance(artist: string, song: string)Hits(year: integer, rank: integer, song: string)Step 7. The Relational AlgebraTranslate the following relational algebra expression into SQL queries:πname(ρR(song, year, composer) ( σyear>1998 ( Song ) ) ⋈ Performance ⋈ ρR(artist, age) (Artist))Add your query to the bottom of lab2.psqlNow, using your relational algebra experience, design SQL queries for the following (and add themto the bottom of lab2.psql):A. The name and age of all artists under the age of 18 who have at least one hit.B. The names of all artists who performed a song composed by “Max Martin”.C. Every composer who wrote at least two different songs in 1998.D. The name of every artist who performed a hit with a lower rank than a previous hit (previous means performed in an earlier year).E. The title of every Song that was never a hit.
Hint:You may find it helpful to use subqueries in your SQL. For example:SELECT name, age FROM Artist WHERE name IN (SELECT artist FROM Performance, Song WHERE Performance.song = Song.name AND song.composer=’Steve Perry’);This would list the names and ages of all artists who performed songs by Steve Perry.You may also find these SQL keywords useful: NATURAL JOIN, CROSS JOIN, UNION, INTERSECT, and EXCEPTAnother useful trick is the ability to rename columns using the “AS” keyword in a SELECT statement.Feel free to populate your Artist, Song, Performance, and Hits tables with sample data for testing. I will ignore anything you insert here.GlitterPopulate your rant database with interesting and useful content using "INSERT INTO" statements at the bottom of your file. The more comments you have and the more interesting they are, the more points you will earn for glitter. Don't forget that in addition to the rants themselves, users can add comments to a rant, so you should populate both tables.Submiting:As usual, save your work and upload your lab2.psql file to http://marmorstein.org/~robert/submit/