Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2013
    Posts
    6

    Unanswered: Help I am SO confused

    I hope I am posting this in the correct section. If not please let me know

    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');

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This looks like Oracle syntax (create database link, and create or replace). As for the question itself, I don't see what the "user requests" are.

  3. #3
    Join Date
    Mar 2013
    Posts
    6
    Thats the same thing I was thinking as far as the user requests.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, users are like that. you may have to get used to it. ;-)

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by MCrowley View Post
    Well, users are like that. you may have to get used to it. ;-)
    Consumers, not users. One who consumes a resource. Users are those individuals who put you in a bad place for their consumption.

    Edit: Drug dealers are users.

  6. #6
    Join Date
    Mar 2013
    Posts
    6
    Ok so I just got confirmation the section My teacher said "Write hypothetical queries for the user requests"

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    We are not going to do your homework for you.
    If you want assistance, you will need to ask a specific question.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Mar 2013
    Posts
    6
    We are not going to do your homework for you.
    If you want assistance, you will need to ask a specific question.
    I dont need anyone to do my hw for me if it was that simple i could have asked someone in my class. I know how to write query statements we learned that in week one. The problem that I am having is what queries should you write for a financial comapny

    I thought I was on the right path with these
    Show all variables for employee status whose role is “manager”

    select * from company where role = “manager”

    Show Name, employeeID, from employee whose role is employee

    select name, employeeID, from employee where role = “employee” order by name

    Count personel listed in the employee table

    select count(*) from employee where series = 1

    Count personel listed in the Employee table, distinguishing between regular employees and managers

    select count(*), role from employee where series = 1 group by role


    Show the average charged in activity, distinguishing between gender, and excluding activities with 0 total wealth, or where the gender of the client was uncertain.

    select avg(total), sex from activity where series = 1 and sex <> 0 and total <> 0 group by sex

    But when I showed it to my professor she said that those are queries yes but not for a financial company... that to me makes no sense.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ruckus09 View Post
    But when I showed it to my professor she said that those are queries yes but not for a financial company... that to me makes no sense.
    You are right. That doesn't make any sense.
    Either you don't understand the assignment, or the professor doesn't understand database design. In my experience, both possibilities are equally probable.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The only thing I can see is that you shouldn't be using "select *". Enumerate your columns.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •