Kevin McMahon

CSMN 661

Lab Project

November 19, 2003

Part 1

 

1)      The purpose of this database is to provide a means for tracking all changes made to the ground system of an operational satellite.  The database will contain the identification of each component of the system, as well as document changes made to each component, who made the changes, and why, and who approved the changes.  This has been somewhat simplified from the summary paper due to the five table restriction for the project.  Each person is only a member of one group, FOT, SERB, or Developer.  The SERB is only responsible for approving and/or denying change requests.  The FOT is only responsible for requesting changes, and the developers are only responsible for implementing changes.

 

2)      This database contains five tables:

 

a)      Components – This table contains the information that defines each component of the system.

 

Column Name

Description

Data type

Component_No

The unique identifier for each piece of software/hardware

Number(8)

Version/Model_No

The version number for software or the model # for hardware.

Variable character(30)

Designer/Manufacturer

The software designer responsible for software or the manufacturer for hardware.

Variable character(30)

Active Date

The date this version number or model number became active.

Date

 

b)      Change_Request – This table contains all of the information pertinent to any requested change to the system.

 

Column Name

Description

Data Type

Request_No

The unique numerical identifier for each requested change.

Number(8)

Effects

This will be a short description of the anticipated effects of the change.

Variable character(255)

Justification

The change requester will supply a short justification for the change.

Variable character (255)

Request_date

The date the request is made.

Date

Status

This will be: approved, denied, or pending.

Variable character (8)

Status date

The date the status was last updated.

Date

 

c)      Developers – This table will contain the information for all of the people responsible for writing/updating software tools and replacing/updating hardware.

 

Column Name

Description

Data Type

Developer_No

The unique numerical identifier for each developer.

Number(8)

Company

The company that the developer is employed by.

Variable Character (50)

Contact_No

The Phone number of the developer.

Number(10)

Name

The name of the developer.

Variable Character (50)

Email

The email address of the developer.

Variable Character(100)

 

d)      FOT – This table will contain the information defining all the members of the Flight Operations Team.

Column Name

Description

Data type

Emp_No

The unique identifier for each FOT Member.

Number(8)

Name

The name of the FOT Member.

Variable character(50)

Contact_No

The phone number for the FOT member.

Number(10)

Email

The email address of the FOT member.

Variable Character(100)

 

 

e)      SERB – This table will contain the information that defines each member of the Software Engineering Review Board.

 

Column Name

Description

Data type

Emp_No

The unique identifier for each SERB Member.

Number(8)

Name

The name of the SERB Member.

Variable character(50)

Contact_No

The phone number for the SERB member.

Number(10)

Email

The email address of the SERB member.

Variable Character(100)

 

3) Below is the ERD.

 

 

 

 

 

4)      I decided to use rule 2 from the basic conversion rules instead of rule 5 since rule 2 will create fewer tables.  I am more concerned with fewer tables than having null values. 

CREATE TABLE Components (Component_no number(8) NOT NULL, description varchar(255), type char(8), version_model_no varchar(30),

designer_manufacturer varchar(30), active_date DATE,

CONSTRAINT PKComponents PRIMARY KEY (component_no));

 

CREATE TABLE Developers (Developer_No number(8) NOT NULL, Company varchar(50), Contact_No number(10),

Name varchar(50), email varchar(100),

CONSTRAINT PKDeveloper_No PRIMARY KEY (Developer_No));

 

CREATE TABLE FOT (FOT_Emp_No number(8) NOT NULL, Name varchar(50), Contact_No number(10),

email varchar(100),

CONSTRAINT PKFOT PRIMARY KEY (FOT_Emp_No));

 

CREATE TABLE SERB (SERB_Emp_No number(8) NOT NULL, Name varchar(50), Contact_No number(10),

email varchar(100),

CONSTRAINT PKSERB PRIMARY KEY (SERB_Emp_No));

 

 

CREATE TABLE Change_Request (Request_No number(8) NOT NULL, Effects varchar(255), Justification varchar(255),

Request_date DATE, Status Varchar(8), Status_Date DATE, SERB_Emp_No number(8), FOT_Emp_No number(8),

Developer_No number(8),Component_no number(8),

CONSTRAINT PKChange_Request PRIMARY KEY (Request_No),

CONSTRAINT FKSERB_Emp_No FOREIGN KEY (SERB_Emp_No) REFERENCES SERB ON DELETE SET NULL,

CONSTRAINT FKFOT_Emp_No FOREIGN KEY (FOT_Emp_No) REFERENCES FOT ON DELETE SET NULL,

CONSTRAINT FKDeveloper_NO FOREIGN KEY (Developer_No) REFERENCES Developers ON DELETE SET NULL,

CONSTRAINT FKComponent_No FOREIGN KEY (Component_No) REFERENCES Components ON DELETE SET NULL);

The Following is the response from the Oracle server:

 

SQL> CREATE TABLE Components (Component_no number(8) NOT NULL, description varchar(255), type char(8), version_model_no varchar(30),

  2  designer_manufacturer varchar(30), active_date DATE,

  3  CONSTRAINT PKComponents PRIMARY KEY (component_no));

 

Table created.

 

SQL> CREATE TABLE Developers (Developer_No number(8) NOT NULL, Company varchar(50), Contact_No number(10),

  2  Name varchar(50), email varchar(100),

  3  CONSTRAINT PKDeveloper_No PRIMARY KEY (Developer_No));

 

Table created.

 

SQL> CREATE TABLE FOT (FOT_Emp_No number(8) NOT NULL, Name varchar(50), Contact_No number(10),

  2  email varchar(100),

  3  CONSTRAINT PKFOT PRIMARY KEY (FOT_Emp_No));

 

Table created.

 

SQL> CREATE TABLE SERB (SERB_Emp_No number(8) NOT NULL, Name varchar(50), Contact_No number(10),

  2  email varchar(100),

  3  CONSTRAINT PKSERB PRIMARY KEY (SERB_Emp_No));

 

Table created.

 

SQL> CREATE TABLE Change_Request (Request_No number(8) NOT NULL, Effects varchar(255), Justification varchar(255),

  2  Request_date DATE, Status Varchar(8), Status_Date DATE, SERB_Emp_No number(8), FOT_Emp_No number(8),

  3  Developer_No number(8),Component_no number(8),

  4  CONSTRAINT PKChange_Request PRIMARY KEY (Request_No),

  5  CONSTRAINT FKSERB_Emp_No FOREIGN KEY (SERB_Emp_No) REFERENCES SERB ON DELETE SET NULL,

  6  CONSTRAINT FKFOT_Emp_No FOREIGN KEY (FOT_Emp_No) REFERENCES FOT ON DELETE SET NULL,

  7  CONSTRAINT FKDeveloper_NO FOREIGN KEY (Developer_No) REFERENCES Developers ON DELETE SET NULL,

  8  CONSTRAINT FKComponent_No FOREIGN KEY (Component_No) REFERENCES Components ON DELETE SET NULL);

 

Table created.

5)

SQL> describe components;

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 COMPONENT_NO                             NOT NULL NUMBER(8)

 DESCRIPTION                                                    VARCHAR2(255)

 TYPE                                                           CHAR(8)

 VERSION_MODEL_NO                                               VARCHAR2(30)

 DESIGNER_MANUFACTURER                                          VARCHAR2(30)

 ACTIVE_DATE                                                    DATE

 

SQL> spool off

SQL> describe developers;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 DEVELOPER_NO                              NOT NULL NUMBER(8)

 COMPANY                                            VARCHAR2(50)

 CONTACT_NO                                         NUMBER(10)

 NAME                                               VARCHAR2(50)

 EMAIL                                              VARCHAR2(100)

 

SQL> describe FOT;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 FOT_EMP_NO                                NOT NULL NUMBER(8)

 NAME                                               VARCHAR2(50)

 CONTACT_NO                                         NUMBER(10)

 EMAIL                                              VARCHAR2(100)

 

SQL> describe SERB;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 SERB_EMP_NO                               NOT NULL NUMBER(8)

 NAME                                               VARCHAR2(50)

 CONTACT_NO                                         NUMBER(10)

 EMAIL                                              VARCHAR2(100)

 

SQL> describe change_request;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 REQUEST_NO                                NOT NULL NUMBER(8)

 EFFECTS                                            VARCHAR2(255)

 JUSTIFICATION                                      VARCHAR2(255)

 REQUEST_DATE                                       DATE

 STATUS                                             VARCHAR2(8)

 STATUS_DATE                                        DATE

 SERB_EMP_NO                                        NUMBER(8)

 FOT_EMP_NO                                         NUMBER(8)

 DEVELOPER_NO                                       NUMBER(8)

 COMPONENT_NO                                       NUMBER(8)

SQL> spool off;

 

6)      The SQL statements to insert are below:

 

insert into FOT VALUES ('1', 'Kevin McMahon', '3015551234', 'kevin@email.com');

insert into FOT VALUES ('2', 'James Johnson', '3015551235', 'james@email.com');

insert into FOT VALUES ('3', 'Dave Davidson', '3015551236', 'dave@email.com');

insert into FOT VALUES ('4', 'Ronald Gupta', '3015551237', 'ronald@email.com');

insert into FOT VALUES ('5', 'Leon Jones', '3015551238','leon@email.com');

 

insert into Components VALUES ('1', 'Front end processor number one', 'hardware','FEP-120', 'IBM', '1-DEC-1995');

insert into Components VALUES ('2', 'Front end processor number two', 'hardware','FEP-120', 'IBM', '1-dec-1995');

insert into Components VALUES ('3', 'Workstation xt1ws1', 'hardware','Sparc-120','Sun Microsystems', '1-dec-1995');

insert into Components VALUES ('4', 'Generic Inferential Executor', 'software','4.1', 'CSC', '23-oct-2003');

insert into Components VALUES ('5', 'FORMATS', 'software', '3.5' ,'FDF', '12-nov-2000');

 

insert into SERB VALUES ('1', 'Freddy Kreuger', '4105551234', 'fred@email.com');

insert into SERB VALUES ('2', 'Frodo Baggins', '4105551235', 'frodo@email.com');

insert into SERB VALUES ('3', 'bilbo baggins', '4105551236', 'bilbo@email.com');

insert into SERB VALUES ('4', 'Sammy Hagar', '4105551237', 'sammy@email.com');

insert into SERB VALUES ('5', 'homer simpson', '4105551238', 'homer@email.com');

 

insert into Developers VALUES ('1', 'CSC', '2405551234', 'John Malkovich','john@email.com');

insert into Developers VALUES ('2', 'CSC','2405551235','Lara Croft', 'lara@email.com');

insert into Developers VALUES ('3', 'SGT', '2405551236', 'Patrick Ramsey','patrick@email.com');

insert into Developers VALUES ('4', 'HTSI','2405551237', 'Jane doe', 'jane@email.com');

insert into Developers VALUES ('5', 'FDF', '2405551238','Bart Simpson' , 'bart@email.com');

 

 

 

insert into change_request VALUES ('1', 'allow FORMATS to convert PSAT files correctly', 'flight dynamics changed the format of the PSAT file, this change is needed to properly process it.', '10-oct-2000', 'complete', '12-nov-2000', '1', '5','5','5');

insert into change_request VALUES ('2', 'allow the inferential executor to command battery management', 'This would ease the work of the FOT since this is done often.', '31-dec-2000', 'denied', '10-jan-2001', '2', '2', '1', '4');

insert into change_request VALUES ('3', 'update the xt1ws1 workstation to a sparc-150', 'This will allow the workstation to run faster','11-nov-2003', 'pending', '11-nov-2003',NULL , '1',NULL , '3');

insert into change_request VALUES ('4', 'replace front end processor 1 with a newer model', 'This should allow for more stable commanding', '2-NOV-1995', 'complete', '1-dec-1995', '3','4','4','1');

insert into change_request VALUES ('5', 'replace front end processor 1 with a newer model', 'This should allow for more stable commanding', '2-nov-1995', 'complete', '1-dec-1995', '3','4','4','1');

 

 

The results of the insert statements are below:

SQL> insert into FOT VALUES ('1', 'Kevin McMahon', '3015551234', 'kevin@email.com');

 

1 row created.

 

SQL> insert into FOT VALUES ('2', 'James Johnson', '3015551235', 'james@email.com');

 

1 row created.

 

SQL> insert into FOT VALUES ('3', 'Dave Davidson', '3015551236', 'dave@email.com');

 

1 row created.

 

SQL> insert into FOT VALUES ('4', 'Ronald Gupta', '3015551237', 'ronald@email.com');

 

1 row created.

 

SQL> insert into FOT VALUES ('5', 'Leon Jones', '3015551238','leon@email.com');

 

1 row created.

 

SQL>

SQL> insert into Components VALUES ('1', 'Front end processor number one', 'hardware','FEP-120', 'IBM', '1-DEC-1995');

 

1 row created.

 

SQL> insert into Components VALUES ('2', 'Front end processor number two', 'hardware','FEP-120', 'IBM', '1-dec-1995');

 

1 row created.

 

SQL> insert into Components VALUES ('3', 'Workstation xt1ws1', 'hardware','Sparc-120','Sun Microsystems', '1-dec-1995');

 

1 row created.

 

SQL> insert into Components VALUES ('4', 'Generic Inferential Executor', 'software','4.1', 'CSC', '23-oct-2003');

 

1 row created.

 

SQL> insert into Components VALUES ('5', 'FORMATS', 'software', '3.5' ,'FDF', '12-nov-2000');

 

1 row created.

 

SQL>

SQL> insert into SERB VALUES ('1', 'Freddy Kreuger', '4105551234', 'fred@email.com');

 

1 row created.

 

SQL> insert into SERB VALUES ('2', 'Frodo Baggins', '4105551235', 'frodo@email.com');

 

1 row created.

 

SQL> insert into SERB VALUES ('3', 'bilbo baggins', '4105551236', 'bilbo@email.com');

 

1 row created.

 

SQL> insert into SERB VALUES ('4', 'Sammy Hagar', '4105551237', 'sammy@email.com');

 

1 row created.

 

SQL> insert into SERB VALUES ('5', 'homer simpson', '4105551238', 'homer@email.com');

 

1 row created.

 

SQL>

SQL> insert into Developers VALUES ('1', 'CSC', '2405551234', 'John Malkovich','john@email.com');

 

1 row created.

 

SQL> insert into Developers VALUES ('2', 'CSC','2405551235','Lara Croft', 'lara@email.com');

 

1 row created.

 

SQL> insert into Developers VALUES ('3', 'SGT', '2405551236', 'Patrick Ramsey','patrick@email.com');

 

1 row created.

 

SQL> insert into Developers VALUES ('4', 'HTSI','2405551237', 'Jane doe', 'jane@email.com');

 

1 row created.

 

SQL> insert into Developers VALUES ('5', 'FDF', '2405551238','Bart Simpson' , 'bart@email.com');

 

1 row created.

 

SQL>

SQL>

SQL>

SQL> insert into change_request VALUES ('1', 'allow FORMATS to convert PSAT files correctly', 'flight dynamics changed the format of the PSAT file, this change is needed to properly process it.', '10-oct-2000', 'complete', '12-nov-2000', '1', '5','5','5');

 

1 row created.

 

SQL> insert into change_request VALUES ('2', 'allow the inferential executor to command battery management', 'This would ease the work of the FOT since this is done often.', '31-dec-2000', 'denied', '10-jan-2001', '2', '2', '1', '4');

 

1 row created.

 

SQL> insert into change_request VALUES ('3', 'update the xt1ws1 workstation to a sparc-150', 'This will allow the workstation to run faster','11-nov-2003', 'pending', '11-nov-2003',NULL , '1',NULL , '3');

 

1 row created.

 

SQL> insert into change_request VALUES ('4', 'replace front end processor 1 with a newer model', 'This should allow for more stable commanding', '2-NOV-1995', 'complete', '1-dec-1995', '3','4','4','1');

 

1 row created.

 

SQL> insert into change_request VALUES ('5', 'replace front end processor 1 with a newer model', 'This should allow for more stable commanding', '2-nov-1995', 'complete', '1-dec-1995', '3','4','4','1');

 

1 row created.

 

SQL>

SQL> spool off

 

 

 

7)                  A) The first view will allow a SERB member to find any new change requests that have not been approved or denied.

CREATE VIEW new_request AS

SELECT request_no, effects, justification, FOT.Name, FOT.Contact_No, components.description

FROM change_request, FOT, components

WHERE status='pending'

AND FOT.FOT_EMP_No=Change_request.FOT_EMP_No

AND change_request.component_no=components.component_no;

 

select Name, contact_no, effects, description

from new_request;

 

SQL> select Name, contact_no, effects, description

  2  from new_request;

 

NAME                                               CONTACT_NO                                      

-------------------------------------------------- ----------                                      

EFFECTS                                                                                            

----------------------------------------------------------------------------------------------------

DESCRIPTION                                                                                        

----------------------------------------------------------------------------------------------------

Kevin McMahon                                      3015551234                                      

update the xt1ws1 workstation to a sparc-150                                                       

Workstation xt1ws1     

 

B) The second view will allow anyone to get a quick view of the current status of all components.

 

create view snapshot AS

select Component_no, Version_model_no, designer_manufacturer, active_date

from components;

         

SELECT * from snapshot;

 

SQL> select * from snapshot;

 

COMPONENT_NO VERSION_MODEL_NO               DESIGNER_MANUFACTURER          ACTIVE_DA               

------------ ------------------------------ ------------------------------ ---------               

           1 FEP-120                        IBM                            01-DEC-95                

           2 FEP-120                        IBM                            01-DEC-95               

           3 Sparc-120                      Sun Microsystems               01-DEC-95               

           4 4.1                            CSC                            23-OCT-03               

           5 3.5                            FDF                            12-NOV-00               

 

SQL>

                                                    

8) 

a) Query table components to show which manufacturers/designers we use.

SELECT UNIQUE designer_manufacturer

FROM  components;

 

SQL> SELECT UNIQUE designer_manufacturer

  2  FROM  components;

 

DESIGNER_MANUFACTURER                                                          

------------------------------                                                 

CSC                                                                            

FDF                                                                            

IBM                                                                             

Sun Microsystems                                                               

 

SQL>

 

b) Query table developers to show us the unique companies that are dealt with:

SELECT UNIQUE company

from Developers;

 

SQL> SELECT UNIQUE company

  2  from Developers;

 

COMPANY                                                                        

--------------------------------------------------                             

CSC                                                                             

FDF                                                                            

HTSI                                                                           

SGT                                                                             

 

SQL>

 

c) Query table FOT to show all members and their email addresses:

SELECT name, email

FROM FOT;

 

 

NAME                                                                           

--------------------------------------------------                             

EMAIL                                                                          

--------------------------------------------------------------------------------

James Johnson                                                                   

james@email.com                                                                

                                                                               

Dave Davidson                                                                   

dave@email.com                                                                 

                                                                               

Ronald Gupta                                                                   

ronald@email.com                                                               

                                                                               

 

NAME                                                                           

--------------------------------------------------                             

EMAIL                                                                          

--------------------------------------------------------------------------------

Leon Jones                                                                      

leon@email.com                                                                 

                                                                               

 

SQL>

 

d) Query table SERB to show all members and their email addresses:

 

select name, email

FROM SERB;

 

SQL> select name, email

  2  FROM SERB;

 

NAME                                                                           

--------------------------------------------------                              

EMAIL                                                                          

--------------------------------------------------------------------------------

Freddy Kreuger                                                                  

fred@email.com                                                                 

                                                                               

Frodo Baggins                                                                  

frodo@email.com                                                                

                                                                               

bilbo baggins                                                                  

bilbo@email.com                                                                

                                                                               

 

NAME                                                                           

--------------------------------------------------                             

EMAIL                                                                          

--------------------------------------------------------------------------------

Sammy Hagar                                                                     

sammy@email.com                                                                

                                                                               

homer simpson                                                                   

homer@email.com                                                                

                                                                               

 

SQL>

 

E) Query the table change_request to see which requests were denied.

SELECT request_no, effects, status_date

FROM change_request

WHERE status='denied';

 

SQL> SELECT request_no, effects, status_date

  2  FROM change_request

  3  WHERE status='denied';

 

REQUEST_NO                                                                      

----------                                                                     

EFFECTS                                                                        

--------------------------------------------------------------------------------

STATUS_DA                                                                      

---------                                                                      

         2                                                                      

allow the inferential executor to command battery management                   

10-JAN-01                                                                      

                                                                                

 

SQL> spool off

 

 

9)      I’m lost on this one right now, every time I run something I either get way too much information or no information.

 

10) This query shows all updates that have not been approved or declined. 

select request_no

from change_request

where status in ('approved','pending');

 

SQL>

SQL> select request_no

  2  from change_request

  3  where status in ('approved','pending');

 

REQUEST_NO                                                                      

----------                                                                     

         3 

 

PART 2

 

1.

            a) The first update is designed to change the phone number for FOT membet number 1.

 

SQL> UPDATE FOT

  2  SET contact_no='2405686349'

  3  WHERE FOT_Emp_No='1';

 

1 row updated.

 

SQL>

 

            b) The second update is designed to set a new baseline.  If an inventory is done and a new baseline is created, the active dates of all components must be updated.  Assuming all items have been accurately tracked, nothing but the active date should be updated.

 

SQL> UPDATE Components

  2  SET active_date='25-nov-2003';

 

5 rows updated.

 

SQL> spool off

 

2)

            a) Delete an FOT member that has quit.

 

SQL> delete from FOT where FOT_EMP_NO=1;

 

1 row deleted.

 

            b) Delete the entire SERB because its function has been replaced by the FOT, they can now approve any changes.

 

SQL> delete from SERB;

 

5 rows deleted.

 

SQL> spool off

 

 

 

Home Outdoors  Various School Projects Rat Babies Wacky Test Results Contact Kevin McMahon