Adding, modifying and deleting data using SQL

You have a database that was created like this:

CREATE TABLE Nimike (
NimikeID INTEGER     NOT NULL,
Nimike   VARCHAR(64) NOT NULL,
CONSTRAINT Nimike_PK
   PRIMARY KEY (NimikeID)

);

CREATE TABLE Tehtava (
TehtavaID   INTEGER     NOT NULL,
TehtavaNimi VARCHAR(64) NOT NULL,
CONSTRAINT Tehtava_PK
   PRIMARY KEY (TehtavaID)

);

CREATE TABLE Projekti (
ProjektiID   INTEGER     NOT NULL,
ProjektiNimi VARCHAR(64) NOT NULL,
Alkamispvm   DATE        NOT NULL,
Paattymispvm DATE        NOT NULL,
CONSTRAINT Projekti_PK
   PRIMARY KEY (ProjektiID)

);

CREATE TABLE Henkilo (
Email     VARCHAR(64) NOT NULL, 
Etunimi   VARCHAR(32) NOT NULL,
Sukunimi  VARCHAR(32) NOT NULL, 
PuhNro    VARCHAR(32) DEFAULT '-',
Tyonimike INTEGER     NOT NULL, 
CONSTRAINT Henkilo_PK
   PRIMARY KEY (Email),
CONSTRAINT Henkilo_FK_T
   FOREIGN KEY (Tyonimike)
   REFERENCES Nimike (NimikeID)
      ON UPDATE NO ACTION
      ON DELETE NO ACTION

);

CREATE TABLE Tekee (
Henkilo  VARCHAR(64) NOT NULL, 
Tehtava  INTEGER     NOT NULL,
Projekti INTEGER     NOT NULL,
CONSTRAINT Tekee_PK
   PRIMARY KEY (Henkilo, Tehtava, Projekti),
CONSTRAINT Tekee_FK_H
   FOREIGN KEY (Henkilo)
   REFERENCES Henkilo (Email)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Tekee_FK_T
   FOREIGN KEY (Tehtava)
   REFERENCES Tehtava (TehtavaID)
      ON UPDATE CASCADE
      ON DELETE NO ACTION,
CONSTRAINT Tekee_FK_P
   FOREIGN KEY (Projekti)
   REFERENCES Projekti (ProjektiID)
      ON UPDATE CASCADE
			ON DELETE CASCADE

);

Copy the respective Access database to yourself. Here is the vocabulary needed:

• nimike = title
• tehtävä = assignment, job, task
• projekti = project
• henkilo = person
• tekee = does
• etunimi = first name
• sukunimi = last name
• puhNro = phone number
• tyonimike = professional title
• nimi = name
• alkamispvm = starting date
• paattymispvm = ending date

Write the following addings in SQL in the correct order. Run every operation and save every query.

All the addings do not necessarily work. If you get an error message, make sure you understand why. The success of addings depends on the definitions of primary keys and referential integrities.

1. Add to the Nimike relation

   NimikeID	Nimike
   1	Programmer (Ohjelmoija)

2. Add to the Nimike relation

   NimikeID	Nimike
   2	Designer (Suunnittelija)

3. Add to the Tehtava relation

   TehtavaID	TehtavaNimi
   1	Programmes (Ohjelmoi)

4. Add to the Tehtava relation

   TehtavaID	TehtavaNimi
   2	Project manager (Projektipäällikkö)

5. Add to the Projekti relation

   ProjektiID	ProjektiNimi	Alkamispvm	Paattymispvm
   1	WWW	1.2.2000	1.6.2000

6. Add to the Projekti relation

   ProjektiID	ProjektiNimi	Alkamispvm	Paattymispvm
   2	Macro	1.3.2000	1.5.2000

7. Add to the Projekti relation

   ProjektiID	ProjektiNimi	Alkamispvm	Paattymispvm
   3	WWW	2.3.2000	2.6.2000

8. Add to the Henkilo relation

   Email	Etunimi	Sukunimi	Puhnro	Tyonimike
   tommi.j.lahtonen@jyu.fi	Tommi	Lahtonen	2602746	2

9. Add to the Henkilo relation

   Email	Etunimi	Sukunimi	Puhnro	Tyonimike
   peheinon@mit.jyu.fi	Petri	Heinonen	2602746	1

10. Add to the Henkilo relation

    Email	Etunimi	Sukunimi	Puhnro	Tyonimike
    eskheis@mit.jyu.fi	Esko	Heiskanen	2600000	0

11. Add to the Tekee relation

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	1	2

12. Add to the Tekee relation

    Henkilo	Tehtava	Projekti
    peheinon@mit.jyu.fi	1	1

13. Add to the Tekee relation

    Henkilo	Tehtava	Projekti
    eskheis@mit.jyu.fi	1	1

14. Add to the Tekee relation

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	2	1

15. Add to the Tekee relation

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	2	2

16. Add to the Tekee relation

    Henkilo	Tehtava	Projekti
    peheinon@mit.jyu.fi	2	1

17. Add to the Tekee relation

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	1	3

18. Add to the Tekee relation

    Henkilo	Tehtava	Projekti
    tommi.j.lahtonen@jyu.fi	3	3

19. Add to the Tekee relation

    Henkilo	Tehtava	Projekti
    peheinon@mit.jyu.fi	2	3

Write the following updatings in SQL in this order and save every query.

All the updatings may not work. If you get an error message, make sure you see the point. The definitions of the primary keys and referential integrities affect the success of the updatings.

Some updating operations may affect more than one relation (referential integrity). After every operation, check which relations have changed and make sure you understand why.

1. Update the name of project 1 (projektiID = 1) in the Projekti relation to Webproject 1 and the projectID (projektiID) to 4.
2. Update the first title (nimikeID = 1) in the Nimike relation to coder.
3. Update the professional title (tyonimike) for every person in the Henkilo relation to 1.
4. Update the job (tehtava) in the Tekee relation to 2 for all those that work in the project 2 and have peheinon@mit.jyu.fi as an e-mail address.
5. Change the titleID (NimikeID) 1 in the Nimike relation to 3.
6. Change the titleID (NimikeID) 2 in the Nimike relation to 4.

Write the followoing deletions in SQL, save each query and run them in this order

And again, in case of a an error message, make sure you únderstand why the operation failed. Hint: referential integrities :)

And more about the beloved referential integrity: Some deletions may affect more than one relation. After every deletion, check which relations have changed and why.

1. Delete all the projects (Projekti) with the name WWW from the Projekti relation.
2. From the Tehtava relation, delete all jobs with the TehtavaID 1.
3. From the Nimike relation, delete all titles having a NimikeID bigger than 2.