From the following information, you must complete the steps required.
A Project consists of several Tasks. A Task always belongs to a single Project. For each Task we receive several Invoices. Each Invoice is shipped by a Supplier. A Supplier may send several Invoices. An Invoice consists of several Items. In reality, an Item is made up of a Product (internal product number of our company), but a Product Number can be found in several different Invoice Items.
- For a Project we also have the following attributes: # project, project name, project description, estimated project amount, start date and project end date.
- For a Task we also have the following attributes: # task, task name, description, duration in hours.
- For an Invoice we also have the following attributes: # Invoice, date of invoice
– For a Supplier we also have the following attributes: # supplier, supplier name, supplier address, supplier city, supplier phone.
– For an Item we also have the following attributes: # item, quantity
- For a product we also have the following attributes: # product, name, description, unit price
Assumptions：
- # Project is unique
- # Task always starts with 1 for each Project
- # Provider is unique
- # A supplier’s invoice is unique (however, one, two or more vendors may have the same set of invoice number)
- # Product is unique
- # Item always starts with 1 for each Invoice
Note: Indicate your attributes in your schema and indicate your assumptions if necessary.
Steps to be taken:
1. Represent the above data as a tree or entity-relationship model
2. Transform your entity-relationship tree or model into standardized relational tables by highlighting the representative keys.
3. Normalize the tables to the 3rd normal shape (3NS)
Use the following commands (all UNIX commands are lowercase)
– the passwd command allows you to change your password
– the ls command is equivalent to DIR
– the more or cat command is equivalent to TYPE
– the logout command will terminate your session
– the lpr file command prints the file
– The pwd command will give you your current path
– the man command_name command gives you access to help on the operating system
– the logout (or exit) command will terminate your session
– the editor command is called vi (i for insert, ESC to exit from insert, wq to save and exit, q to exit without saving)
Note: You must take the server dim-oracle.uqac.ca (port 22 in Telnet and SFTP)
• Write to a file the commands allowing you to create tables in SQL command (see example in the account of the portal of the course SQL table creation – Oracle).
•
• Create the database tables from an SQL file with the editor of your choice:
•
• [Trd149 …] $ cat filename.sql (check the description of the file)
• [Trd149 …] $ sqlplus (opening SQL mode)
• Enter user-name: trd149_XX (replace XX with the 2 digits of your account)
• Enter password: (enter password for your account)
• Sql> @filename (loading and executing file filename.sql)
• Sql> desc table_name (allows to see the structure on the screen)
•
• You must submit:
• – your FNN normalization to the 3rd NS
• – the file containing your requests to create the tables
• – a screen prints tables that you created with the command desc
•
• Note: You can save information from a session to a file with sqlplus
• The command is: sql> spool filename.lis
• To stop the entry: sql> spool off