Scribd
Upload
8 views3 pages

Company Data Requirements for Database

The document outlines the data requirements for a company organized into branches, detailing the relationships between employees, clients, and suppliers. It specifies the attributes for each entity and describes how to convert this information into a database schema using an Entity-Relationship diagram. Additionally, it lists various tasks to be performed on the database, such as querying employee information and sales data.

Uploaded by

emilieag573
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views3 pages

Company Data Requirements for Database

The document outlines the data requirements for a company organized into branches, detailing the relationships between employees, clients, and suppliers. It specifies the attributes for each entity and describes how to convert this information into a database schema using an Entity-Relationship diagram. Additionally, it lists various tasks to be performed on the database, such as querying employee information and sales data.

Uploaded by

emilieag573
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Project

consider the following Company data requirements.


The company is organized into branches. Each branch has a unique
number, a name, and a particular employee who manages it.

The company makes its money by selling to clients. Each client has a name
and a unique number to identify it.

The foundation of the company is its employee. Each employee has a


name, birthday, sex, salary and a unique number.

An employee can work for one branch at a time, and each branch will be
managed by one of the employees that work there. We will also want to
keep track of when the current manager started as manager.

An employee can acts as a supervisor for other employees at the branch,


an employee may also acts as the supervisor for employees at other
branches. An employee can have at most one supervisor.

A branch may handle a number of clients, with each client having a name
and a unique number to identify it. A single client may only be handled by
one branch at a time.
Employees can work with clients, controlled by their branch to sell their
stuff. If necessary multiple employees can work with the same client. We
will want to keep track of how many francs worth of stuff each employee
sells to each client they work with.

Many branches will need to work with suppliers to buy inventory. For each
supplier, we will keep track of their name and the type of product they are
selling the branch. A single supplier may supply products to multiple
branches.

The above information is a Company data requirements document.


How do you go about converting it into a database schema if you are given
a document like this one? The first thing would be to create an Entity-
Relationship diagram.
ER Model

1. Implement the physical model corresponding to the ER Model above to


obtain the database using the DBMS of your choice. (Name of the
database = company)

2. Insert the following data


3. Carry out the following tasks:
A. Find all employee
B. Find all employee ordered by salary
C. Find all employee ordered by salary
D. Find the first 5 employees in the table
E. Find the forename and surenames names of all employees in the table

F. Find out all the different genders


G. Find the number of employees
H. Find the number of femal employees born after 1st of Jan 1970
I. Find Average salary
J. Find Average salary for male
K. Find the sum of all salary
L. Find out how many males and females are there
M. Find the total sales of each salesman
N. Find the total sales of each salesman
O. Find any client’s who are an LLC
P. Find any client’s who are an LLC
Q. Find any employee born in october
R. Find all employee and their branch
S. Find all branches and the names of their managers
T. Find all branches and the names of their managers
U. Find the names of all employees who have sold over 30, 000
V. Find all clients who are handled by the branch that Michael Scott
manages, assuming you know his ID

You might also like