MsSQL

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesMsSQL

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old April 9th, 2013, 07:15 PM
ruckus09 ruckus09 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Apr 2013
Posts: 1 ruckus09 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 11 m 46 sec
Reputation Power: 0
Help I am so confused

So I have a class assignment and ive been going through my notes and textbook but I am SO confused. Here is my assignment. "Write queries for the user requests"
I have been going through everything and I am completely confused thanxs for helping me out

Page 1
Fragmentation and allocation:
Condition:
P1 : serviceType = 'tax' - 1
P2 : serviceType = 'audit' - 2

Primary fragmentation on Employee, Service :
Employeei : σ Pi (Employee) i = 1, 2
Servicei : σ Pi (Service) i = 1, 2

Derived fragmentation on Activity:
Activityi : σ Pi (Activity) i = 1, 2

Client table is unfragmented. It will be created in DB1 and replicated to DB2.

Page2
DB1 - serviceType = tax DB2 - serviceType = audit
-- Create Employee table
CREATE TABLE Employee
(
employeeID CHAR (4) NOT NULL PRIMARY KEY CHECK
(employeeID BETWEEN 1 AND 1000),
name VARCHAR2 (30) NOT NULL ,
serviceType VARCHAR2 (10) CHECK
(serviceType = 'tax')
) ;


-- Create Service table
CREATE TABLE Service
(
serviceCode CHAR (3) NOT NULL PRIMARY KEY CHECK
(serviceCode BETWEEN 1 AND 10000),
description VARCHAR2 (20) ,
serviceType VARCHAR2 (10) CHECK
(serviceType = 'tax')
) ;


-- Create Activity table
CREATE TABLE Activity
(
clientID CHAR (4) NOT NULL ,
employeeID CHAR (4) NOT NULL ,
serviceCode CHAR (3) NOT NULL ,
serviceDate DATE NOT NULL PRIMARY KEY,
amountCharged NUMBER
) ;


-- Create Client table
CREATE TABLE Client
(
clientID CHAR (4) NOT NULL PRIMARY KEY,
name VARCHAR2 (30) NOT NULL ,
address VARCHAR2 (50)
) ;

-- Add Employee FK to Activity
ALTER TABLE Activity
ADD CONSTRAINT fk_employee_activity
FOREIGN KEY (employeeID)
REFERENCES Employee (employeeID) ;

-- Add Service FK to Activity
ALTER TABLE Activity
ADD CONSTRAINT fk_service_activity
FOREIGN KEY (serviceCode)
REFERENCES Service (serviceCode) ;

-- Add Client Foreign Key to Activity tables
ALTER TABLE Activity
ADD CONSTRAINT fk_client_activity
FOREIGN KEY ( clientID)
REFERENCES Client (clientID) ;



-- Create Employee table
CREATE TABLE Employee
(
employeeID CHAR (4) NOT NULL PRIMARY KEY CHECK
(employeeID BETWEEN 1001 AND 2000),
name VARCHAR2 (30) NOT NULL ,
serviceType VARCHAR2 (10) CHECK
(serviceType = 'audit')
) ;


-- Create Service table
CREATE TABLE Service
(
serviceCode CHAR (3) NOT NULL PRIMARY KEY CHECK
(serviceCode BETWEEN 10001 AND 20000),
description VARCHAR2 (20) ,
serviceType VARCHAR2 (10) CHECK
(serviceType = 'audit')
) ;


-- Create Activity table
CREATE TABLE Activity
(
clientID CHAR (4) NOT NULL ,
employeeID CHAR (4) NOT NULL ,
serviceCode CHAR (3) NOT NULL ,
serviceDate DATE NOT NULL PRIMARY KEY,
amountCharged NUMBER
) ;


-- Add Employee FK to Activity
ALTER TABLE Activity
ADD CONSTRAINT fk_employee_activity
FOREIGN KEY (employeeID)
REFERENCES Employee (employeeID) ;


-- Add Service FK to Activity
ALTER TABLE Activity
ADD CONSTRAINT fk_service_activity
FOREIGN KEY (serviceCode)
REFERENCES Service (serviceCode) ;
CREATE TABLE STATEMENTS
Create Table Notes:

DB1 - Check constraint on serviceType - tax
- Check constraint on employeeID - range between 1 - 1000
- Check constraint on serviceCode - range between 1 - 10000

DB2 - Check constraint on serviceType - audit
- Check constraint on employeeID - range between 1001 - 2000
- Check constraint on serviceCode - range between 10001 - 20000

Transparency tier:
In order to create gobal transparency, we're creating links and views in each database.
CREATE PUBLIC DATABASE LINK DB2 USING
DB2.company.us.com

CREATE PUBLIC DATABASE LINK DB1 USING
DB1.company.us.com


VIEWS:

DB1 DB2
CREATE VIEW Employee_all AS
SELECT * FROM Employee
UNION
SELECT * FROM Employee@DB2;

CREATE VIEW Service_all AS
SELECT * FROM Service
UNION
SELECT * FROM Service@DB2;

CREATE VIEW Activity_all AS
SELECT * FROM Activity
UNION
SELECT * FROM Activity@DB2;

CREATE VIEW Client_all AS
SELECT * FROM Client;
CREATE VIEW Employee_all AS
SELECT * FROM Employee
UNION
SELECT * FROM Employee@DB1;

CREATE VIEW Service_all AS
SELECT * FROM Service
UNION
SELECT * FROM Service@DB1;

CREATE VIEW Activity_all AS
SELECT * FROM Activity
UNION
SELECT * FROM Activity@DB1;

CREATE VIEW Client_all AS
SELECT * FROM Client@DB1;

Page 3
Additional integrity support: Trigger to support global foreign key clientID of Activity

CREATE OR REPLACE TRIGGER tr_activity_client
BEFORE INSERT OR UPDATE OF clientID ON Activity
FOR EACH ROW
DECLARE
sClientID VARCHAR(4);
BEGIN
SELECT clientID INTO sClientID
FROM Client@DB1 WHERE clientID = :NEW.clientID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20999, 'Non-existing clientID');

Reply With Quote
  #2  
Old April 18th, 2013, 02:41 AM
EUWindowsHost EUWindowsHost is offline
Permanently Banned
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Apr 2013
Location: http://www.hostforlife.eu
Posts: 12 EUWindowsHost User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 47 m 59 sec
Warnings Level: 10
Number of bans: 1
Reputation Power: 0
You may try to post your inquiry to forums.asp.net. Maybe they can help you out.

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesMsSQL > Help I am so confused


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap