Management systems

1.

Organization Database
Management Systems
Practice 1
01/2024
__________________________________________
Mohammed A. Saleh

2.

SQL
•SQL online
• https://onecompiler.com/mysql
• http://sqlfiddle.com/#!9/078111/1
•How to install mysql
• https://www.youtube.com/watch?v=N3B3OonC2AU
&ab_channel=AmitThinks

3.

Database Application Design
• Design steps:
– Requirement analysis
– Conceptual database design
– Logical database design
– Schema re nement
– Physical database design
– Application and security design
Phase 1
Phase 2

4.

Requirement Analysis
• Statement of Objectives
– Brief statement made by the customer of what
the objectives of the system are
• Requirement Documents
– Expansion of State of Objectives
– Describes what the system is supposed to do, not
how to do it

5.

Conceptual Database Design
• ER model is used at this stage
– What are the entities and relationships?
– What information about these should we store in
the database?
– What are the integrity constraints (or business
rules) that hold?
– A database schema in the ER model can be
represented pictorially in ER diagrams.
– An ER diagram can be mapped into a relational
schema.

6.

Projects (there are two projects, one
every mid semester
• Requirement Analysis
– All the 11 requirements (see attached
project_description file) should be included and
discussed
– Propose at least two extra functions
• Conceptual Design
– Complete ER diagram
• Report:
– No less than 15 pages
– Follow the template provided
• See the attached files

7.

Question 1
• Draw an ER diagram for the following application:
1. Each factory has a unique id
2. More than one factory can be located in the same
city
3. Each product has a unique product id
4. Each product has a color
5. A factory can produce at most one product
6. A product can be made by at most one factory
7. A factory can produce a xed quantity of each
product

8.

Question 1
• Answer:
F.ID
City
Factory
Quantity
Products
Color
P.ID
Product
8

9.

Question 2
Draw ER diagram that satis es the following
requirements (ignore the attributes):
1. People in the movie industry can be actors or
directors.
2. People works in movies.
3. Each movie has to have at least one people
working in it.
4. Movies have characters. Each character can only
belong to exact one movie. If the movie ends up
not released, the character will not exist.
5. Each character has to be played by exact one
actor.

10.

Answer