cs 3630 database design and implementation. assignment 3 style! agreement between database designer...
TRANSCRIPT
CS 3630 Database Design and Implementation
Assignment 3
Style!
Agreement between
database designer and the client.
UserName1_EasyDrive
UserName2_EasyDrive
2
Project Phase I
20 Points
Could lose up to 10 points!
3
Project Phase I
20 Points
Due Monday, March 30
Sign up for groups by 4 pm, March 23
(-2 for each late day)
Members may not receive the same grade!
Report any issues to me.
4
Test 1
5
Assignment7
Create tables with constraints
6
Your Oracle Account
Using EDDB_SQL+ (not SQL PLUS)
•UserName is the same as your UWP username•Followed by @EDDB•Not case sensitive
•Initial Password: UWPUserName1 (all lower case)
Example: yangq1•Password is case sensitive
7
Reset Your Oracle Password
• Must reset password after login the first time
• If you forget your password– Email to HelpDesk at [email protected] from your
UWP email account
– cc to Kenneth M Wiegman (He will do it)
– cc to Qi Yang (I cannot do it)
8
Assignment7
Create tables with constraints
Using Script File
(Program)
Style: could lose five points!
9
UserName_Lab7.sql------------------------------------------------
-- Name : Qi Yang
-- UserName : YangQ
-- Date : 03-13-15
-- Course : CS 3630
-- Description: Drop tables
-- Create tables
-- Insert records
------------------------------------------------
Drop Table test2;
Drop Table test1;
Create table test1 . . .
Desc Test1
Pause
Create Table Test2 . . .
Desc test2
Pause
Insert into test1 . . .
Commit;
Select *
From Test1;
. . . 10
SQL Script File
• Using any text editor outside SQL*Plus
• File extension .SQL
UserName_Lab7.Sql• Multiple SQL commands
• Each command ends with ;
11
Running Script File
• Using either Start or @
SQL> Start file_name_with_full_path
SQL> @file_name_with_full_path
• Use Arrow Keys to get previous commands!
12
Create a Table
Create Table Test1 (
C1 Char(5) Primary Key,
C2 Varchar2(50),
C3 Integer,
C4 Date);
13
Create another Table
Create Table Test1 (
C1 Char(5) Primary Key,
C2 Varchar2(50),
C3 Integer,
C4 Date);
Create Table Test2 (
D1 VARCHAR2(15) Primary Key,
D2 Char(5) references Test1,
D3 Integer);
14
Drop Tables
Drop table test1;
Drop table test2;
Create Table Test1 (
C1 Char(5) Primary Key,
C2 Varchar2(50),
C3 Integer,
C4 Date);
Create Table Test2 (
D1 VARCHAR2(15) Primary Key,
D2 Char(5) references Test1,
D3 Integer);
15
Drop Tables
Drop table test2;
Drop table test1;
Create Table Test1 (
C1 Char(5) Primary Key,
C2 Varchar2(50),
C3 Integer,
C4 Date);
Create Table Test2 (
D1 VARCHAR2(15) Primary Key,
D2 Char(5) references Test1,
D3 Integer);
16
Insert Records
Insert into Test1
Values ('cs363', 's1', 44, '28-feb-12');
Insert into Test1
Values ('cs334', 's2', 45, '29-feb-12');
One record at a time!
Single quotes for string
Date is entered as string
in the default format
17
Insert Records Insert into Test2
Values ('Database', 'cs363', 44);
Insert into Test2
Values ('Windows', Null, 54);
Insert into Test2
Values ('OOP I', 'cs243', 60);
Insert into Test2
Values ('OOP I', 'cs363', 60);
Insert into Test2
Values ('OOP II', 'cs243', 100);
18
Retrieve Records
Select *
From Test1;
Pause
Select *
From Test2;
19
Assignment7
Table names and column names must be exactly the same as specified.
20