Database Lab 4

due Friday, week of the lab

1. In your own database, create (at least) two tables. Insert and modify some data.
   
2. Set up a many:many relationship (at least one) as a table with referential integrity. It may be a reflexive relationship.
   

Your database

psql 

Sources of information/reference:

Syntax:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ AS output_name ] [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ ORDER BY expression [ ASC | DESC ] [, ...] ]

where from_item can be:

[ ONLY ] table_name [ * ]
    [ [ AS ] alias [ ( column_alias_list ) ] ]

.........(there is more)

1. in psql: \h   for a list of commands, then \h insert -- for help on insert, etc.
2. The HTML documentation is on the postgresql web site, get it for the current version.

   What to do:

   The following suggests some examples, please do what makes sense for your eventual project, or seems good for trying out features and experimenting with queries. I would like there to be, in effect, a 1:many and a many:many relationship.
   Set up at least two tables, using CREATE TABLE (and ALTER TABLE as needed) for a one-many relationship, such as people to their phone numbers. (nowadays, many people have a set of more that one phone number. Use a short key, such as a number or initials, or login name, to make the connection.
   
   Declare referential integrity (REFERENCES) in the phone number table, declare a primary key in the people table.
   
   Insert some data. I will query your database, to find out about 'Lin Jensen'. To complete the lab, you should put in some made up information about me. (Assuming your table features people.)
   
   Try out UPDATE and DELETE statements. What happens when you delete a person?
   
   Also, use a table to implement a many:many relationship. A relationship "contacts" giving, for each person, the set of people they desire to contact (phone or send email to, for instance) would be reflexive, that is, from people to people. It would need 2 columns, one for the sender of the emails, the other for the receiver. Similarly, a genealogy could relate parents to children.
   
   Finally, you would probably like an easy way to generate a list of your email contacts (or phone or children). Create a view to do this. A view is simply a query that is stored in the database ("on the server side")
   
   For a business, a useful view would be for one line of an invoice. You'd probably want description and price of an item, and total cost (price * quantity).
   
   For example, to get a list of all the phones of all my friends, I would like to do a query as follows, rather than a complicated set of joins and subqueries:
   

   	select name, phone from contactview 
   	where sender like 'lj%';
   

   Defining a view:

   A view is simply a stored query. You can use it in other queries like it was a table. For example, lets say you have tables:
   
   • orders(customerid, productcode, quantity)
   • products (productcode, prodname, price), and you'd like:
   • orderline (customerid, quantity, prodname, price, total)
   You can create the view orderline as follows:
   

   create or replace view orderline AS
   	select customerid, quantity, prodname, price, (price*quantity) AS total
   	from orders natural join products;
   

   Completion of the lab:

   1. Have your database and tables set up on linux, and have some data in the tables. Have a view defined.
   2. Send me short note using this form saying it is ready, and brief instructions on how to use your view. If you haven't done the assignment, or have changed it for your database, also tell me what it is about, and how the tables are related.
   Your Name:
   Your Username:
   Remarks:
   

   ---

   Prepared by Lin Jensen. back to Database index