RelationalDBDesign
SiteMap
Database Architecture
Database Admin
Managing Users
Managing Objects
Backup Recovery
Admin Tasks
Managing Tables
«Prev
Managing Objects
Space Management
Storage Management Concepts
Data Blocks
Data System Blocks
Segment Role
Oracle extents Data
Setting Extent Size
Tablespace Default
Data Dictionary Storage
Oracle Free Space
Oracle Lists
Space Blocks
Free Space
Table Space Fragmentation
Tablespace Database Objects
Correct Table Space
Space Management Conclusion
Managing Tables
Create Oracle Table
Column Datatypes
Schema Manager Table
Generate Table List
Describing Table Columns
Modify Table Column
Delete Oracle Table
Schema Manager Conclusion
Managing Constraints
Four Basic Constraints
Primary Key Constraints
Database Table Constraints
Create Foreign Constraints
Unique Foreign Key Constraints
Table Trigger
Adding Table Constraints
Implement Column Constraints
Data Dictionary Constraints
Managing Constraints
Oracle Indexes
Oracle Database Index
Oracle Data Index
Index Types
Create Table Index
Separate Indexes-
Alter Index Attributes
Rebuilding Oracle Index
Data Dictionary Views
Drop Index Oracle
Using Oracle Indexes
Managing Privileges
Granting Privileges
Grant Command
Using Security Manager
Listing Table Privileges
Listing Privileges Columns
Revoking Object Privileges
Object Privileges Conclusion
Database Security Roles
Database Roles
Creating Roles
Granting Role Privileges
Default non Default Roles
Password Protected Roles
WITH ADMIN OPTION
Security granted By Roles
Invoker Caller Routine
View Information about Roles
Listing Granted Roles
Drop Database Role
COIN database - Exercise
Course Project: Create table script for the Coin Database
Objective:
Create a script that will create the tables for the
COIN
database.
Exercise scoring
This exercise is worth a total of 24 points. Each of the six commands is worth 4 points. You will be graded on correctness and completeness of code.
Once you have completed your answer submit.
Background and Overview
In the earlier courses in the Oracle DBA curriculum, you learned about the
COIN
database, which is the sample database used throughout this series of courses.
The structure of the
COIN
database is shown in the following diagram:
Coin database consisting of tables: 1) LOT, 2) COINS_IN_LOT, 3) COIN, 4) AUCTION, 5) BID, 6) CLIENT
This exercise works with the
COIN
database (our course project) and tests your new skills in creating table structures.
Download Files
You can download a correct version of the script for this exercise, createtable.sql, from the project download file found on the Resources page.
Instructions
Write a script that contains a series of
CREATE TABLE
commands to create the tables in the
COIN
database. Assign the appropriate datatypes, lengths, and sizes for the columns in each table.
Use the following guidelines to create the columns in each table:
All ID columns are numeric with no scale.
All cost amounts (prices, bids, amounts) are numeric with a scale of 2 and a precision of 11.
All times use the
DATETIME
datatype.
All phone numbers are stored as character strings.
The winning_bidder column refers to the
bidder_client_id
, which refers to the
client_id
, so all of the columns should have identical data types.
Hints
You must use the
CREATE TABLE
syntax for each individual table. Separate individual
CREATE TABLE
statements with semicolons (;) in order to run the script properly in SQL*Plus. End your script with the command
COMMIT
; to make Oracle implement your commands.
Submitting your Exercise
Enter your queries into the text box below. Click the
Submit
button to submit your answers.
Remember that you must submit all your responses to this exercise at once by making use of the textbox below.