LEuwWDzT

1<?php
2/*
3#Create the database and tables;
4Students: Alan  Trope - 337637961 - Fabian Roitman -
5*/
6$user     = "root";
7$password = "123456";
8$dsn      = 'mysql:host=localhost';
9$db       = new PDO($dsn, $user, $password);
10$db->query("CREATE DATABASE IF NOT EXISTS softwareCompany;");
11$db->query("USE softwareCompany;");
12
13$db->query("CREATE TABLE IF NOT EXISTS engineer (
14  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
15  name varchar(255) not null,
16  birthday date not null
17  ");
18
19$db->query("CREATE TABLE IF NOT EXISTS engineerPhones(
20  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
21  phone varchar(30) not null,
22  engineerId int(11) NOT NULL REFERENCES engineer(id) ON DELETE CASCADE)
23  ");
24
25$db->query("CREATE TABLE IF NOT EXISTS engineerAddress(
26  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
27  city varchar(255) not null,
28  street varchar(255) not null,
29  country varchar(255) not null,
30  zip varchar(255) not null,
31  engineerId int(11) NOT NULL REFERENCES engineer(id) ON DELETE CASCADE)
32  ");
33
34$db->query("CREATE TABLE IF NOT EXISTS softwareField (
35  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
36  name varchar(255) not null,
37  specialization varchar(255) not null
38  ");
39
40$db->query("CREATE TABLE IF NOT EXISTS project (
41  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
42  name varchar(255) not null,
43  startdate date not null,
44  description varchar(255) not null
45  ");
46
47$db->query("CREATE TABLE IF NOT EXISTS projectSoftware(
48  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
49  name varchar(50) not null,
50  projectId int(11) NOT NULL REFERENCES project(id) ON DELETE CASCADE)
51  ");
52
53$db->query("CREATE TABLE IF NOT EXISTS milestone(
54  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
55  name varchar(50) not null,
56  startdate date not null,
57  enddate date not null,
58  moneysum decimal(15,2) not null,
59  done tinyint not null,
60  projectId int(11) NOT NULL REFERENCES project(id) ON DELETE CASCADE)
61  ");
62$db = null;
63?>