Deliverables

I didn't put the whole thing in an organised manner, but still it gives you everything you need except ERD. I made an ERD on paper and try to put that on internet as soon as possible.

OK, let's start. :)

I put the sql code for creating database and tables in the last post. You can check it first. After creating the database and tables, you need to populate the tables, meaning you need to enter some information into the database.

This sql code below will do the work for you:

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

INSERT INTO `employees` (`employee_id`, `name`, `address`, `city`, `dob`, `current_job`, `skills`, `projects`) VALUES
(NULL, 'John Paypal', '96 Shadwell Gardens', 'London', '1970-10-20', 'engineer', 'prepare requisitions, checking drawings', 'Millenium Dome, London Crossing'),
(NULL, 'Max Levchin', '126 Kingston Avenue', 'Liecester', '1978-09-25', 'foreman', 'prepare requisitions, checking drawings', 'Millenium Dome, Channel Tunnel'),
(NULL, 'Sergey Brin', '95 Queenland Square', 'Manchester', '1980-10-30', 'secretary', 'typing', 'Millenium Dome, London Crossing'),
(NULL, 'Larisa Palmall', '97 Maryland Street', 'Newcastle', '1980-10-25', 'engineer', 'prepare requisitions', 'Millenium Dome, London Crossing, Channel Tunnel'),
(NULL, 'Eric Schmit', '124 Google Street', 'London', '1968-10-22', 'foreman', 'prepare requisitions, checking drawings', 'Millenium Dome, Channel Tunnel'),
(NULL, 'Marissa Meyer', '18 Mary Street', 'Blackpool', '1982-10-24', 'secretary', 'typing', 'Millenium Dome, Channel Tunnel'),
(NULL, 'Maisha Frost', '12 Lower Thames Street', 'London', '1965-10-20', 'secretary', 'typing', 'Millenium Dome'),
(NULL, 'Paul Scott', '23 Lisbon Street', 'Blackpool', '1972-09-15', 'engineer', 'checking drawings', 'London Crossing, Channel Tunnel'),
(NULL, 'Andrew Miliband', '10 Case Street', 'Manchester', '1982-04-10', 'secretary', 'typing', 'London Crossing'),
(NULL, 'Patrick Batchmen', '97 Russkin Street', 'Kent', '1959-11-25', 'engineer', 'checking drawings', 'Channel Tunnel'),
(NULL, 'Adam Garcia', '12 Gobbles Street', 'Kent', '1978-06-20', 'foreman', 'prepare requisitions, checking drawings', 'Millenium Dome, Channel Tunnel'),
(NULL, 'Harriet Harman', '77 Bow Street', 'Blackpool', '1982-10-24', 'secretary', 'typing', 'Channel Tunnel');

INSERT INTO `engineer` (`engineer_id`, `employee_id`, `type_of_degree`) VALUES
(NULL, 1, 'electrical'),
(NULL, 4, 'mechanical'),
(NULL, 8, 'mechanical'),
(NULL, 10, 'electrical');

INSERT INTO `secretary` (`secretary_id`, `employee_id`, `typing_speed`) VALUES
(NULL, 3, 100),
(NULL, 6, 70),
(NULL, 7, 85),
(NULL, 9, 75),
(NULL, 12, 110);

INSERT INTO `married` (`employee_id`, `marriage_date`, `to_whom`) VALUES
(1, '1995-02-08', 'Larisa Palmall'),
(3, '2007-03-19', 'Marissa Meyer'),
(7, '1988-10-26', 'Andrew Miliband');

INSERT INTO `skills` (`skill_no`, `skill_name`, `description`) VALUES
(101020, 'preparing requsitions', 'identifying and checking the quality of the materials'),
(102030, 'checking drawings', 'checking drawings of all the newly installed cupboards');

INSERT INTO `departments` (`dept_name`, `tel_no`) VALUES
('engineering', 44021234561),
('secretarial', 44021234562),
('research', 44021234563),
('sales', 44021234564),
('aqusitions', 44021234565),
('human resources', 44021234566),
('technitions', 44021234567),
('programmers', 44021234568),
('database admins', 44021234569),
('health and safety', 440212345610),
('scientists', 440212345611);

INSERT INTO `vendors` (`name`, `address`, `equipment_type`, `last_meet_date`) VALUES
('Supplyforce inc', '96 shadwell avenue, London', 'soil densimeter', '2009-02-10'),
('Forgenet suppliers inc', '87 monty python street, London', 'ss shape sensor', '2008-12-29'),
('Magnesium Engineering', '123 Fleet Street, Blackpool', 'electric sample ejector', '2009-03-04'),
('Procter inc', '67 Densel Street, Manchester', 'permeameter', '2009-03-17');

INSERT INTO `projects` (`project_no`, `project_name`, `est_cost`) VALUES
(123, 'East London Crossing', 12000000),
(124, 'Channel Tunnel', 15000000),
(125, 'Millennium Dome', 1000000);

INSERT INTO `city` (`city_name`, `city_state`, `population`) VALUES
('London', 'London', 5000000),
('Manchester', 'Manchester', 2000000),
('Kent', 'Kent', 1000000),
('Blackpool', 'Southwestern', 30000),
('Liecester', 'Liecester', 35000);

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

See, if it works fine when you copy and paste this code on phpmyadmin.
Give me a shout, if you have a problem.
Good luck!

Max

2nd part of the assignment.

I was working on the ERD of this assignment, but it will take time and effort to draw it on MS Word or any other software. I just draw it on paper. :) I am such a lazy bastard. :)
Anyway, here we go, the database design. The whole MySQL code is here. It will roughly be like this.

You can open PhpMyAdmin and copy/paste the sql commands there.
You will get exactly the same results you see in the image. Click on the image to make it bigger.

Note: I made some changes on the sql code below when I was populating the whole database tables.

-------------------------------------------------------------------------
create database projectsinc;
use projectsinc;

create table employees
(employee_id int unsigned not null auto_increment primary key,
name varchar(50) not null,
address varchar(50) not null,
city varchar(40) not null,
dob date not null,
current_job varchar(40) not null,
skills varchar(80) not null,
projects varchar(80) not null
);

create table engineer
(engineer_id int unsigned not null auto_increment primary key,
employee_id int unsigned not null,
type_of_degree varchar(20) not null
);

create table secretary
(secretary_id int unsigned not null auto_increment primary key,
employee_id int unsigned not null,
typing_speed int unsigned not null
);

create table married
(employee_id int unsigned not null primary key,
marriage_date date not null,
to_whom varchar(50) not null
);

create table skills
(skill_no int unsigned not null primary key,
skill_name varchar(60) not null,
description text not null
);

create table departments
(dept_name varchar(30) not null primary key,
tel_no int unsigned not null
);

create table vendors
(name varchar(40) not null primary key,
address varchar(60) not null,
equipment_type varchar(60) not null,
last_meet_date date not null
);

create table projects
(project_no int unsigned not null primary key,
project_name varchar(60) not null,
est_cost float(12,2) not null
);

create table city
(city_name varchar(20) not null primary key,
city_state varchar(20) not null,
population int unsigned not null
);
-------------------------------------------------------------------------



Good Luck with rest of the assignments. I hope this code will help.

Max

Assignment for BTEC Data Analysis and Design

Assignment Part 1

1) What different data models are used in databases? Describe and give examples of each.

Data models are tools to facilitate an understanding of business information requirements.

We learnt 3 different data models.

1. Entity – relationship model
2. Relational model
3. Network model

• Entity - relationship model

Description:

Entity – relationship model is the most commonly used data model during analysis.
We first identify the things of interest in the business enterprise and then categorise them into types. Things are called entities and these categorised types are called entity types.
Relationships are made between the entity types. A diagram which shows these relationships are called entity – relationship diagram. Entity types and relationships are both documented. The diagram and the documentation together is called the entity – relationship model.

Example:

We can look at this in an example of teachers and students at CLC.
We can either call teachers and students with their names or we can categorise them into types. Instead of Mr. Michael or Dr. Sahar, we have ‘Teacher’ and instead of John and Radix, we have ‘Student’.

• Relational Model of Data

The most widely used data model for implemented systems is the relational model.
The relational model was originally defined by Codd in 1970 and later described by Date.
According to the fathers of this data model, every model should consist of 3 components:
1. The objects which form the model.

In a relational data model, a database is a set of relations. A relation can be defined in terms of the set product of several domains. For example, Name domain, description domain and price domain are all put together in one table.


2. Model integrity rules.

The model has 2 integrity rules. These rules are not optional; they are part of the model. The rules are:

• Entity integrity

The integrity of an entity is that the entity is always uniquely identified. Every relation has a primary key attribute. Primary key attribute(s) must never be NULL.

• Referential integrity

It defines how relations reference other relations or how tables reference other tables. Referential integrity is the rule concerning foreign key values. Foreign keys are primary keys in other tables. For example: The attribute name is a primary key in the 1st table but it is a foreign key in the 2nd table. ISBN is a primary key in the 2nd table.
We can have NULL foreign key values where relationships are optional.


3. Set of data operators.

Codd originally proposed an extended set of algebra called relational algebra. Required relational operators are specified in order to operate on the database. Relational algebra is difficult to learn and use for non-mathematicians. Relational calculus is was a pre-cursor to SQL. All the proposed operators are mathematically based and non-procedural. The user makes a statement about what they want. Nowadays a standard set of operators we use is SQL. SQL is a part of the data model.

2) Give examples, descriptions and uses of databases that are currently in the market. Which ones figures amongst the top three ones and why?
3) What are the different approaches to datbase design? Give examples of each. Which one is the most popular and why?

I didn't put the answers for the 1st part of the assignment. You can check your hand-outs. All the answers are there.