disc01 - EECS 484 Discussion 1 Introduction to Project 1...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: EECS 484 Discussion 1 Introduction to Project 1 Outline             CAEN and Oracle Account Setup Access your Oracle Account Project 1 Overview Download Your Facebook Data Browse and Manage Your Data in SQL*Plus ER Design Exercise CAEN and Oracle Account Setup     CAEN account to access the CAEN machines, from which you can login to Oracle database Does everyone have a CAEN account?     If you are not a student in CoE, you do not have a CAEN account by default Oracle Account     Each of you should have received an email with the username and initial password If not, please contact me ASAP Access your Oracle Account   Login (ssh) to a CAEN machine       Usually login.engin.umich.edu Use your CAEN account username and password Setup SQL*Plus Environment     source /usr/caen/oracle/local/muscle Login to SQL*Plus   sqlplus <username>/<password> Project 1 Overview Populate database Part 1: ER Diagram   Read the descriptions carefully to identify constraints   Photo Tag     At most ONE tag for each user in a photo Make decision based on your general knowledge and common sense   Separated Entity vs. Attribute   Location Part 2: Relational Model       Convert the ER diagrams into relational table (upcoming lesson on Monday) DDL scripts to create and drop tables Capture as many constraints as possible   Primary Key   Foreign Key   Not Null   Triggers and Sequence Part 4: Insert into tables you created INSERT INTO new_user SELECT user_id, first_name FROM user_information WHERE rownum< 2;     DISTINCT   UNION Triggers and Sequence CREATE SEQUENCE loc_sequence Whenever you insert a row into START WITH 1 LOCATION, the above will INCREMENT BY 1; automatically set the value of CREATE TRIGGER loc_trigger LOC_ID to the next integer in BEFORE INSERT ON LOCATION the sequence. FOR EACH ROW BEGIN SELECT loc_sequence.nextval into :new.LOC_ID from dual; END; . RUN; Part 3: Download your Facebook Data   h"p://apps.facebook.com/eecs_facebookdata/     Downloads your Facebook data and insert them into tables in your Oracle account. You are the only one to access this data Fake Data     SELECT user_id FROM heedokim.public_user_information; Copy the data to your own account   CREATE TABLE new_user AS ( SELECT * FROM heedokim.public_user_information);       DESC new_user; SELECT user_id FROM new_user; Let me know if you do not have access to the fake data Browse your Facebook Data SELECT table_name FROM user_tables;   DESC user_information;   SELECT user_id FROM user_information;   SELECT first_name, last_name FROM user_information WHERE rownum < 3;   column first_name format A20;   column last_name format A20;   ER Diagram Exercise       There are doctors, and each doctor has a name, a salary, and a unique ssn. There are hospitals, and each hospital has an address and a unique name. Each doctor practices at exactly one hospital. There are patients, and each patient has a name. Each patient MUST be associated with exactly one doctor, and no two patients of a given doctor have the same name (though two patients of the different doctors can have the same name). In the database, patient tuples should be automatically deleted if the corresponding doctor tuple is deleted. There are chapters of the American Medical Association, and each chapter has a unique cid and a membership fee. Doctors can be members of zero or more chapters; it is important to maintain the date on which a doctor joined a chapter. Each chapter has a coordinator, and only doctors can serve as chapter coordinators. No doctor can be coordinator for more than one chapter. ...
View Full Document

This note was uploaded on 12/08/2011 for the course EECS 484 taught by Professor Staff during the Winter '08 term at University of Michigan.

Ask a homework question - tutors are online