SQL Assignment Set up your own database, and Set up the tables you think are app

Question

SQL Assignment Set up your own database, and Set up the tables you think are appropriate for an organization that takes orders and sells or distributes something. (Or something else of comparable complexity.) It could be a store selling fruits and vegetables, a food bank, a business selling computer parts, pottery, space ships, towels, or digital watches; it could even be a group distributing CD's of open-source software In your own database, 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.) . 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'; Completion of the Assignment Write a short note and brief instructions on how to use your view. Also tell me what it is about, and how the tables are relatec

Explanation / Answer

How a View can be created :-

---------------------------------------------------

  CREATE OR REPLACE VIEW view_name AS <complex query>

For example (here People and Contacts are Data Tables) , below view make ready made data of Emails

can be used directly by any application

CREATE OR REPLACE VIEW Emails AS select p.Name, c.Email from People p , Contacts c

where p.pid = c.pid

In the above example a Vitual   table is created with Fields Name , Email fields

select * from Emails

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.