Based upon Assignment 4, 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. In the instructions that follow, I will suggest a simple project. However, you may do something equivalent (fulfilling the requirements just stated), and you can look ahead to lab 6. Actually, I would like you to create (some of) the tables for the organization you have described in Assignment #4. Your database Has the name of username, and you "own" it. To access it, just type the command: psql You may now proceed to create tables. (use CREATE TABLE and ALTER TABLE statements.) Sources of information/reference: There are several places to find reminders of the syntax of SQL. They take the form of 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) In these, [ ] means optional, I separates alternatives, { } means one alternative must be present, ... means more of the same. To find the help: 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;

Based upon Assignment 4, 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. In the instructions that follow, I will suggest a simple project. However, you may do something equivalent (fulfilling the requirements just stated), and you can look ahead to lab 6. Actually, I would like you to create (some of) the tables for the organization you have described in Assignment #4. Your database Has the name of username, and you "own" it. To access it, just type the command: psql You may now proceed to create tables. (use CREATE TABLE and ALTER TABLE statements.) Sources of information/reference: There are several places to find reminders of the syntax of SQL. They take the form of 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) In these, [ ] means optional, I separates alternatives, { } means one alternative must be present, ... means more of the same. To find the help: 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;