Tasks

Create a logical data model (ER diagram) for the database of Sville AS in MySQL Workbench, based on the description below. Make your own assumptions if you think the thesis text is unclear or ambiguous. Paste a picture of the finished computer model in the answer.

Create a MySQL database from the model (forward engineer), and insert a few rows of sample data in each table. Tip: If two tables have foreign keys to each other, then you can register data in two steps. In the first step, just leave the foreign keys NULL. Paste pictures of the sample tables in the answer.

The database of Sville AS

Sville AS is a company that performs maintenance work on railway lines in Vestfold and Telemark. The company has approx. 10 offices with a total of approx. 100 employees. Assignments that are in addition to daily operations are organized as projects. The company wants to build a database to manage such projects.

Each employee has one of the offices as a meeting place. In addition, the first name, surname, date of birth, position, and salary must be stored for each employee.

About each office, the phone number should be stored, in addition to a unique code and a place name. Each office has a manager.

Each project has a project manager and is assigned a unique project number. In addition, a short, textual description of the project and of the place where the work is to be carried out is registered.

The projects set requirements for specific types of competence, such as certificates for various vehicles and rail equipment, first aid courses, and approvals to perform special tasks related to safety. It is desirable to be able to register how many people with a special competence are needed on the various projects.

The competence requirements are per. now divided into the overall competence categories safety, type approval, traffic safety, and other certificates, but it may be relevant to expand with more categories. For each competence category, one of the employees has been appointed as responsible for keeping up to date in this area.

In order to staff the projects correctly, the company must know which employees have which competence. Competence is time-limited, for example in that employee A received competence K on a date D1, and is approved until date D2.

The company always allocates employees to projects throughout working days. It is thus not possible to work on one project until 12 and on another the rest of the day. However, it is possible to work on project X on Monday, project Y on Tuesday, and then again project X on Wednesday. Employees who are not allocated to other projects on a specific day are basically available and can be deployed on a project.

Appendix: Competence requirements divided by category

Type approvals - responsible Arne Haslum

220

Plate 128-2

Levhan 128-B

LM2

LM4

LM5

The Rob 5144

OBW 10 5015

OBW 10 5085

Robel 5417

Robel 22 T.M.S.7.

Security - responsible Karianne Movatn

Access permit 15

Energy fitter

Regulation course FSH

First aid course

GHPS Modul 3

Grant access 15

Grant access permit 15

Grant an accompanying permit 15

Approved head for connection 17

Approved head of safety 18

Contact wiring fitter

Head of coupling 17

Head of Security 18

Accompanying permit 15

Installer basic course

Deputy 12

Traffic safety - responsible Arne Haslum

551

552 Train conductor

553

553 Train Freight Forwarder

556

561

ATC-2

Traffic

FSH

First aid course

Ice shocks / 2 people

Leader el. Safety

M1

Other certificates - responsible Johanne Amundsen

2 people power disconnection

ATC

Excavator

Wheel loader

Ice shocks

2nd grade

Kombimaskin

Crane driver's license

Leader el. Safety

The county

Rail welds

Tractor

Tractor excavators

Truck

Vegetation spraying

Appendix B: Machine types

The machines that the company has at its disposal can be placed in the following machine types, represented in CSV format:

Type Code; Description

2000; Shift tractor Skd 220

2005; Renskebukk Levahn 116

2006; Renskebukk Plasser & Theurer TUF 100

2008; Ballastvogn Plasser & Theurer MFS -40

2010; Ballast cleaning plant Plasser & Theurer RM 76 HR

2013; Ballast distributor Sollinger Htte SK 69 N

2014; Ballast Distributor Place & Theurer SSP 103

2015; Ballast Distributor Place & Theurer USP 303

2016; Ballast distributor Matisa R 20 LS

2020; Track Stabilizer Plasser & Theurer DGS 42 N

2025; Packing Machine Place & Theurer MU 07-16

2026; Packing Machine Place & Theurer MU 08-16

2027; Packaging machine Matisa B 20 L

2028; Packing Machine Place & Theurer MU 08-16 SP

2029; Combi packaging mask Pl. & Th. 08-275 (6007-6010)

2030; Kombi-pakkem.Pl. & Th.Unimat 08-275 3S (6011)

2031; Kombi-pakkem.Pl. & Th.Unimat 08-475 4S (6012)

2032; Line Wrapper Place & Theurer 09-32 CSM

2033; Combi packaging mask Pl. & Th. 07-275

2034; Line Packing Machine Place & Theurer 09-3X

2040; Sporbyttetog Plasser & Theurer SMD 80

2041; Robel Long Rail Train 40.61

2042; Desec Tracklayer TL 70

2043; Welding Machine Place & Theurer K 355 APT

2045; Loader Tractor Plasser & Theurer OBW 10

2046;Lastetraktor Robel 54.17-3-8-9-11-12

2047;Lastetraktor Beilhack GKW 12/1

2048; Levahn 123 tractor

2049; Geismar Donelli VMT 845 GR (5072)

2050;Lastetraktor Robel 54.22-2-3-4-7

2051;Lastetraktor Pl. & Th. OBW 10 N (5085-5086)

2052; Loader Tractor Plasser & Theurer OBW 10 083 (5087)

2053; Levahn 128-2-B load tractor

2054; Loader Tractor Plasser & Theurer OBW 10.046 / 1 (5120)

2055; Pedestrian tractor Geismar Donelli VMT 850 PL GR (5126)

2056;Lastetraktor Robel 54.22-10/11

2057;Lastetraktor Robel 54.22-25/26

2058;Lastetraktor Robel/Hamar 54.17-3b--9b-11b HMR

2065; Auditing trolley Levahn LM2

2066; Auditing trolley Levahn LM3

2067; Auditing trolley Robel 57.07, LM4

2068; Auditing trolley Geismar VMT 966C / UM1 / UM2 LM5

2069; Audit trolley Windhoff FU 100 type A and B LM6

2075; Snfres veraasen V 130 HS

2076; Self-propelled snow thrower Lameco SNP 082 N

2077; Self-propelled snow thrower Beilhack DiR.3

2078; Self-propelled snow blower veraasen SR 340 H

2085;Snsuger Lameco SNS 101

2090; Drum trolley Kalmar Drum trolley 87

2091; Drumvogn Geismar W40 DER

2092; Drum trolley NSB Hamar KV-01

2093; Portalkran Donelli PD 350

2094;Lastetraktor Robel 54.10

2095;Lastetraktor Robel 54.12

2096; Shift tractor Skd 217

2097; Shift tractor Skd 218

2098; Shift tractor Skd 213

2100; Motor vehicle Fiat Type Y1 BK

undefined

This is Database assignment. programming will write in sql.