saB2MivU

1<?php
2/*
3#Create the database and tables;
4Students: AlanTrope - 337637961 - Fabian Roitman -
5We used Foreign keys instead of Junction Tables as those are far more representative and faster to query.
6If needed we can change to junctions tables in some minutes.
7*/
8$user     = "root";
9$password = "123456";
10$dsn      = 'mysql:host=localhost';
11$db       = new PDO($dsn, $user, $password);
12$db->query("CREATE DATABASE IF NOT EXISTS softwareCompany;");
13$db->query("USE softwareCompany;");
14
15
16$db->query("CREATE TABLE IF NOT EXISTS project (
17id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
18name varchar(255) NOT NULL,
19startdate date NOT NULL,
20description varchar(255) NOT NULL
21");
22
23$db->query("CREATE TABLE IF NOT EXISTS projectSoftware(
24id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
25name varchar(50) NOT NULL,
26projectId int(11) NOT NULL REFERENCES project(id) ON DELETE CASCADE)
27");
28
29$db->query("CREATE TABLE IF NOT EXISTS milestone(
30id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
31name varchar(50) NOT NULL,
32startdate date NOT NULL,
33enddate date NOT NULL,
34moneysum decimal(15,2) NOT NULL,
35done tinyint NOT NULL,
36projectId int(11) NOT NULL REFERENCES project(id) ON DELETE CASCADE)
37");
38
39$db->query("CREATE TABLE IF NOT EXISTS softwareField (
40id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
41name varchar(255) NOT NULL,
42specialization varchar(255) NOT NULL
43");
44
45$db->query("CREATE TABLE IF NOT EXISTS engineer (
46id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
47name varchar(255) NOT NULL,
48birthday date NOT NULL,
49specialization int(11) NOT NULL REFERENCES softwareField(id)
50");
51
52$db->query("CREATE TABLE IF NOT EXISTS works(
53id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
54projectId int(11) NOT NULL REFERENCES project(id) ON DELETE CASCADE)
55engineerId int(11) NOT NULL REFERENCES engineer(id) ON DELETE CASCADE)
56");
57
58$db->query("CREATE TABLE IF NOT EXISTS grades(
59id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
60value int(11) NOT NULL,
61projectId int(11) NOT NULL REFERENCES project(id) ON DELETE CASCADE)
62engineerId int(11) NOT NULL REFERENCES engineer(id) ON DELETE CASCADE)
63");
64
65$db->query("CREATE TABLE IF NOT EXISTS engineerPhones(
66id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
67phone varchar(30) NOT NULL,
68engineerId int(11) NOT NULL REFERENCES engineer(id) ON DELETE CASCADE)
69");
70
71$db->query("CREATE TABLE IF NOT EXISTS engineerAddress(
72id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
73city varchar(255) NOT NULL,
74street varchar(255) NOT NULL,
75country varchar(255) NOT NULL,
76zip varchar(255) NOT NULL,
77engineerId int(11) NOT NULL REFERENCES engineer(id) ON DELETE CASCADE)
78");
79
80$db = null;
81?>