Database Design

1
Database Design

• And Implementation

2
Done so far

• Started a design of your own data model
• In Software Engineering, recognised the processes
  that occur in the system.

3
To do

• Implement the data model using CREATE statements
• In SQL Server
• In Oracle
• Populate the data model using inserts
• Design
• Queries that will be relevant to the system
• Transactions that will cause the data to flow
  through the system.

4
Data model

• Give the attributes
• data types
• Check constraints
• Note any checks that need to be done that cannot
  be done in the CREATE statement.
• Later, these can be implemented through
  triggers.
• Decide on the sample of data you want to use to
  populate your model.

5
Implementing your model in SQL Server

• Keep your SQL Server SQL separate from your
  Oracle SQL.
• Set up a SQL Server implementation directory for
  your .SQL files.
• E.g. U\databases\SQLServer\MYMODEL.SQL
• Implement the CREATEs in SQL Server
• Populate the model using INSERT statements.

6
Implementing your model in SQL Server

• Keep your SQL Server SQL separate from your
  Oracle SQL.
• Set up a SQL Server implementation directory for
  your .SQL files.
• E.g. U\databases\SQLServer\MYMODEL.SQL
• Implement the CREATEs in SQL Server
• Populate the model using INSERT statements.

7
Copy to Oracle

• Set up an Oracle implementation directory
• E.g. U\databases\Oracle\
• Copy the SQL from your SQL Server directory
• Note the differences in syntax between Oracle
  create and SQL Server create.
• Change the Oracle SQL.

8
Design your processes

• Using the processes uncovered in Software
  Engineering
• Design transactions to implement those processes
• Design queries that would be useful in your
  system.
• When you think about this, it can help you to
  decide what data you want in your system.

9
More Data Manipulation language

• We have done
• Insert
• Select
• To do
• Delete
• Update

10
Delete

• To delete from a table
• Delete from lttablegt where ltconditiongt
• E.g.(see handout)
• Delete from STUDENT where MINIT like B
• Note
• 1. This may not work. Look at the handout and
  decide why.

11
Update

• Update lttablenamegt
• set ltfieldgt ltexpressiongt
• Where ltconditiongt
• e.g.
• Update student
• set FNAME Sidney
• Where SID 2222