Lab4 : CASE Tools, Physical Database Design, Database Security and Data Warehouse

PART A. CASE TOOLS FROM ER DIAGRAMS

Using a CASE Tool (e.g. MS Visio Professional edition 2002 Database Model Diagram), draw one of the following ERD
  1. the conceptual data model of Ms. Ehree Hospital case study prepared by your team in Lab. 3.
  2. The diagram in Fig 11.13, on page 365 of the textbook.
A short tutorial on MS Visio data modeling is available from the TA announcement web page. You are also encourage to look at the help information in MS Visio.

Deliverables

PART B. PHYSICAL DATABASE DESIGN

Examine and execute the following script referring to the tables in lab2: 

SET DEFINE OFF;
DROP SYNONYM movie;
DROP TABLE movie;

CREATE TABLE movie (
	id INTEGER NOT NULL, title VARCHAR(70), yr DECIMAL(4), 
	score FLOAT, votes INTEGER, director INTEGER, 
	PRIMARY KEY (id), FOREIGN KEY (director) REFERENCES actor(id)
);
INSERT INTO movie SELECT * FROM f02g40.movie;

COMMIT;
NOTE: The steps listed above have been added on 11/12/02 (1pm) to address an Oracle issue as explained in TA-announcement . These steps should be executed only once. 
SET TIMING ON;

DROP INDEX movie_yr;

SELECT count(*) FROM movie, casting WHERE id=movieid AND yr=1966;

CREATE INDEX movie_yr ON movie(yr);

SELECT count(*) FROM movie, casting WHERE id=movieid AND yr=1966;
The above steps run a query with and without non-clustering index on movie.yr These steps include Oracle SQLplus commands to measure the response time of queries. Units of measure are not relevant but relative magnitudes are. Larger number indicate slower response times. You may also ignore the error message from the "drop index" statement for the first run of this script. Assume that clustering indexes are available on primary key columns for each table. There is no index on the foreign keys.

Deliverables

PART C. DATABASE SECURITY

Create the following tables in your own Oracle account. 'XY' stands for last two digits in your Oracle account. For example, an account, f02g07 will have actor07, movie07 and casting07. Do not add data to these tables. 
    CREATE TABLE actorXY 
          (id INTEGER NOT NULL, name VARCHAR(35), PRIMARY KEY (id));

    CREATE TABLE movieXY (id INTEGER NOT NULL, title VARCHAR(70), 
	yr DECIMAL(4), score FLOAT, votes INTEGER, director INTEGER, 
	PRIMARY KEY (id), FOREIGN KEY (director) REFERENCES actor(id));

    CREATE TABLE castingXY (
	movieid INTEGER NOT NULL, actorid INTEGER NOT NULL, 
	ord INTEGER, PRIMARY KEY (movieid, actorid), 
	FOREIGN KEY (movieid) REFERENCES movie(id), 
	FOREIGN KEY (actorid) REFERENCES actor(id));
Suppose you wish to share parts of the data in your tables (ActorXY, MovieXY, CastingXY) with a partner oracle account. Partner account can refer to the tables in your account using dot notation, e.g. owner.table name in various queries and commands. ) Use VIEWs, and GRANT /REVOKE statements to provide minimum security extension for following independent scenarios
  1. Partner team can retrieve all properties of movies released in last 30 years.
  2. Partner team can retrieve and modify (insert, update and delete) any table except CastingXY and can grant any these privileges to other users .
  3. Partner can retrieve or modify (insert, update and delete) all attributes of MovieXY except votes and score.
  4. Revoke all permission on all tables from partner. Can partner account still find the names of tables defined by your account?
Work with another team to test your solutions. Please find another team to work with on this lab.

Deliverables

PART D. DATA WAREHOUSE

Consider the Country table we used in lab1. Consider GDP to be the measure attribute. Consider REGION to be a dimension attribute. And also attributes of population and area can be treated as dimension attributes provided those are discretized properly. For example, consider dimension of POPULATION_QUARTILE as a dimension attribute defined on the population column. Top 25 percent of the countries by population get a value of 1 for population_quartile column. Next 25 percent of the countries by population get a value of 2 for population_quartile column and so on. Similarly one may define AREA_QUARTILE as a dimension attribute using the values from the area column.

Download and review lab4_country_fact.sql to create country_fact table from the country table used in Lab. 1. You will need to drop the country table created in your account for Lab. 1 using lab4_country.sql .

Deliverables