ECET 450: Laboratory 1
This laboratory exercise introduces the SQL language using Oracle’s SQL*Plus command-line interface. In particular, the student executes and edits SQL commands in the command line interface to create and populate a small table.
Following the specified directions, and using your assigned user name, password, and host string, log in to SQL*Plus.
Change your password to something you will remember. Type the following at the SQL> prompt ALTER USER user_name IDENTIFIED BY new_password;
In order to do this laboratory exercise, it is necessary for you to save all your command-line inputs and Oracle’s responses. At the SQL> prompt the general form of the spool command is spool p:/. For example, the professor might use the following command spool f:/ecet450/lab01a/lab01a.lst. Be sure to create all the folders in the path first.
Use the CREATE TABLE and INSERT INTO commands to create the EMPLOYEE table shown on the next page. Use the decimal data type for numeric attributes. Use the edit commands as needed to fix any errors along the way.
When you are done, use the SELECT statement to display your finished table.
At the SQL> prompt enter the spool off command to terminate collection of your dialog. Your dialog is not placed into your list file until you execute this command. If you exit from SQL*Plus before executing the spool off command, your dialog will be lost.
Type EXIT to terminate SQL*Plus.
Print your list file, be sure to write your name on it, and submit it at the time and in the manner specified by your instructor.
EMP_ID EMP_LNAME EMP_DEP EMP_HIRE_DATE EMP_PAY
321 Lincoln 2 12-JAN-05 15.45
324 Filmore 1 11-FEB-06 12.75
327 Johnson 3 04-MAR-07 10.37
Notes on Attribute Definitions:
EMP_ID is the Primary Key and is a fixed character string.
EMP_LNAME is a variable length character string no larger than 16 characters.
EMP_DEP is a small decimal representing the number of dependents for tax purposes.
EMP_HIRE_DATE is the employee’s hire date.
EMP_PAY is the employee’s hourly rate that a 5, 2 format should accommodate.
This laboratory exercise introduces the concept of the Entity Relationship Diagram (ERD). The work should be done using the MS Visio tool using a crow’s foot ERD. The finished ERD should not contain a many-to-many relationship. Note that this exercise is concerned with only a small part of a larger DB.
A college wants to add extracurricular activities to its DB containing student data. The student table contains the student’s identifier, his/her name, his/her address, his/her primary telephone number, and his/her birth date. The new activity table contains data about the college’s recognized extracurricular activities including the activity’s name, the name of the activity’s advisor, and the advisor’s office location. The college wants to also track the number of years that a specific student participates in a specific extracurricular activity.
Obviously more than one student must participate in an extracurricular activity in order for the college to recognize the activity. Also a specific student may participate in zero, one, or several extracurricular activities. As a result, the student table and the activity table have a many-to-many relationship. In addition a specific student may have participated in one activity, e.g. IEEE student chapter, for three years and in another activity, e.g. TAP honorary, starting this year.
Using the MS Visio drawing tool, draw a crow’s foot ERD that describes the Student table, the Activity table, any other needed tables, and the relationships among these tables. Be sure to include the following items in your diagram:
Update all attribute names so that they partially or completely contain the name of the table in which they reside.
Identify the attribute or the group of attributes that is the primary key of each table.
Modify your ERD so that it does not include any many-to-many (M:N) relationships.
Indicate the connectivity of each relationship, i.e. 1:1, 1:M, and M:N, using the correct crow’s foot symbols.