Fundamentals of Information System

1
Fundamentals of Information System

• Tutorial 6 - SQL(2)

2
Necessary Functions of SQL

• Modification on Tuples
• Insertion (tuple) insert clause
• Deletion (tuple) delete clause
• Updates (tuple) update clause
• Domain Data Type defined by user
• Creating (domain) create domain clause
• Deleting (domain) drop domain clause
• Modification on Database Schema
• Creating (table) create table clause
• Deleting (table) drop clause
• Modifying (table) alter table clause

3
Modification on Tuples

• Insertion
• Insert Into R(attr) Values ()
• Insert Into R(attr) (Select clause)
• Deletion
• Delete From R Where cond
• Updates
• Update R Set attrvalue Where cond

4
Some Common Data types
5
Domain

• Create Domain
• Create Domain name As type Default value
• Parameter type is a type in SQL, e.g. char(10),
  varchar(10), integer,
• Alter Domain
• Alter Domain name Set Default value
• Delete Domain
• Drop Domain name
• Domain Constraints
• Constraint name Check(cond)
• Check( valuegtx )
• Check( value not null )
• Check( value In ( x1, x2, / Select clause ) )
• Place below the Create Domain clause

6
Modification on Schema

• Create Table
• Create Table name (
• attribute1 domain1/type1 Default value1/Not
  Null,
• attribute2 domain2/type2 Default value2/Not
  Null,
• )
• Delete Table
• Drop Table name
• Modify Table
• Alter Table name Add attribute domain/type
• Alter Table name Drop attribute

7
Integrity Constraints

• Domain Constraints
• Referential Integrity
• Foreign Key
• Assertions (Lecture Notes)
• Specify a condition that the database always
  satisfies
• Triggers (Lecture Notes)
• Specify an action to be taken when a condition is
  satisfies
• Functional Dependencies (Chapter 7)

8
Primary Key / Unique

• Define the Primary Key in the Create Table clause
• Multiple attributes
• PName1 Type/Domain Not Null,
• PName2 Type/Domain Not Null,
• Primary Key( PName1, PName2 ),
• Single attribute
• PName Type/Domain Primary Key,
• Keyword Unique
• Can be used the same as Primary Key to specify
  attributes that must be unique in the table
• Primary Key can be used at most ONCE in a
  relation
• Unique can be used MANY times in one relation

9
Foreign Key

• Definition
• A set of attributes of one relation
• R1 and R2 are two relation with primary key K1
  and K2
• R1s subset K is a Foreign Key Referencing to K2
  in relation R2 ? For any tuple t1 in R1, there
  exists at least one tuple t2 in R2, such that
  t1.Kt2.K2
• After defining the Foreign Key, reject Insertion
  / Deletion / Update Errors
• In SQL, how to define Foreign Key In Create Table
  clause
• Foreign Key (attr) References table
• On Delete Cascade
• On Update Cascade,
• Delete Cascade / Update Cascade
• Delete the tuples / Update the tuples attributes
  when there are Deletion / Update Errors

10
More Commands in Oracle

• List all the existing tables in your account
• select owner, table_name
• from all_tables
• where owner SE3550_xxx
• Show the details of a table
• describe relation
• Show disk quota left
• select from user_ts_quotas

11
Exercise

• Given relation schema
• Student (SID, SName, Gender, CNum)
• SID Students ID
• SName Students Name
• Gender Students Gender
• CNum The number of courses taken by him / her
• Primary Key SID
• Course (CID, CName, SNum)
• CID Courses ID
• CName Courses Name
• SNum The number of students taking this course
• Primary Key CID
• Register (SID, ClD)

12
Exercise (Using SQL)

• Create this Database
• Define appropriate domain and domain constraint
  for Gender
• Gender char(1)
• Constraint Gender F or M
• Create the three tables
• The Default Value of CNum and SNum must be 0
• Gender and SName and CName cannot be Null
• Define the primary keys for each table
• Define appropriate foreign keys
• Register( SID ) -gt Student
• Register( CID ) -gt Course
• Define appropriate Triggers to maintain CNum and
  SNum
• Condition Insertion/Deletion/Updates of Register
• Define appropriate Assertions to guarantee that
  one student cannot select more than 6 courses
• Define appropriate Assertions to guarantee that
  one course cannot be selected by more than 100
  students

13
Solution

• Create Domain GenderType as char(1)
• Constraint GValueTest Check( value in (F, M)
  )
• Create Table Student(
• SID integer Not Null,
• SName varchar(100) Not Null,
• Gender GenderType Not Null,
• CNum integer Default 0,
• Primary Key( SID )
• )

14
Solution

• Create Table Course(
• CID integer Not Null,
• CName varchar(100) Not Null,
• SNum integer Default 0,
• Primary Key (CID)
• )
• Create Table Register(
• SID integer Not Null,
• CID integer Not Null,
• Primary Key ( SID, CID ),
• Foreign Key (SID) References Student
• On Delete Cascade
• On Update Cascade,
• Foreign Key (CID) References Course
• On Delete Cascade
• On Update Cascade
• )

15
Solution

• Create Trigger RegInsert After Insert On
  Register(
• Referencing New Row As NewR
• Foreach Row
• Begin Atomic
• Update Student Set CNumCNum1 Where
  SIDNewR.SID
• Update Course Set SNumSNum1 Where
  CIDNewR.CID
• End
• )
• Create Trigger RegDelete After Delete On
  Register(
• Referencing Old Row As OldR
• Foreach Row
• Begin Atomic
• Update Student Set CNumCNum-1 Where
  SIDOldR.SID
• Update Course Set SNumSNum-1 Where
  CIDOldR.CID
• End
• )

16
Solution

• Create Trigger RegUpdate After Update On
  Register(
• Referencing
• Old Row As OldR,
• New Row As NewR
• Foreach Row
• Begin Atomic
• Update Student Set CNumCNum-1
• Where SIDOldR.SID
• Update Student Set CNumCNum1
• Where SIDNewR.SID
• Update Course Set SNumSNum-1
• Where CIDOldR.CID
• Update Course Set SNumSNum1
• Where CIDNewR.CID
• End
• )

17
Solution

• Create Assertion CNumCons Check(
• Not Exists(
• Select
• From Student
• Where CNumgt6
• )
• )
• Create Assertion SNumCons Check(
• Not Exists(
• Select
• From Course
• Where SNumgt100
• )
• )

18
Thanks