Database Assignment 4: Database Concepts, ERDs, and PostgreSQL Setup

This assignment delves into database concepts and practical application through several tasks. The first task explores the advantages of a Database Management System (DBMS) for a hotel, comparing it to a flat file system, and examining the interaction of people with the hardware, software, procedures, and data within a DBMS. The subsequent tasks focus on database design, requiring the creation of Entity-Relationship Diagrams (ERDs) using Crow's Foot notation. These tasks involve analyzing business rules for scenarios including movies and directors, and doctors and patients, to determine entities, relationships, optionality, and plurality. Another task involves designing an ERD for a company, including departments, projects, and employee information. Finally, the assignment concludes with instructions for students to install PostgreSQL, an open-source database system, for further database programming practice. This assignment aims to provide a comprehensive understanding of database design and implementation.

Assignment #4
Practice task 1. Database Concepts
Imagine that a 3 stars «Deluxe hotel» in Nur-Sultan maintains its data in flat files on a file system.
To assist in reserving rooms and booking guests, the Deluxe hotel use a custom application that
accesses these flat files. The hotel sometimes accesses the files directly. With this scenario in mind,
address each of the topics below:
Topic 1: The Advantages of Database Management Systems. Identify and describe several
advantages that a database system would offer Deluxe hotel over their current data management
solution.
Topic 2: Five Major Components of a DBMS. It describes database systems as having five major
components: hardware, software, people, procedures, and data. If the hotel were to adopt a
database management system, describe ways in which people would interact with and be
responsible for the hardware, software, procedures, and data. You don’t need to try to provide an
exhaustive list, because that would be voluminous, but only an indication that you have thought
about how people would be responsible for and interact with each of the other four components.
Practice task 2. Movies & Directors
Review the following business rules regarding movies and directors to complete this part.
- A director films one or more movies
- A movie is filmed by one director
- A genre applies to at least 10 movies
- A movie is released in a year
The business rules above specify all of the relevant entities; however, some business rules do not
specify both sides of the relationship. To get started, please
1. List the names of all entities provided in the business rules above.
2. Identify the entities that are related to each other.
3. Identify the optionality and plurality constraints on both sides of each relationship, where
possible.
4. Create a complete list of business rules. The new list should not add additional entities, but
should specify the optionality and plurality constraints for both sides of all relationships.
5. Create an entity-relationship diagram using Crow’s Foot notation that reflects your list of
business rules.

Practice task 3. Doctors and Patients
Review the following business rules regarding doctors and patients to complete this part.
- Every patient has own doctor
- Doctors can have one or more patients
- A patient may have multiple appointments with multiple doctors in the clinic
- One doctor attends each appointment
- Each appointment results in a bill for the patient.
The business rules above specify all of the relevant entities; however, some business rules do
not specify both sides of the relationship. To get started, please
1. List the names of all entities provided in the business rules above.
2. Identify the entities that are related to each other.
3. Identify the optionality and plurality constraints on both sides of each relationship, where
possible.
4. Create a complete list of business rules. The new list should not add additional entities, but
should specify the optionality and plurality constraints for both sides of all relationships.
5. Create an entity-relationship diagram using Crow’s Foot notation that reflects your list of
business rules.
Practice task 4.
A foreign business man, current chairman, built up a brand-new company in Kazakhstan. The
company must consist of many departments. Each department has a unique name, unique number
and a particular person who manages each department. Each department controls many projects.
Each project has unique number, name and description. The company is very sensitive about their
employees since the company reputation is very vital for the chairman. They keep each employee's
name, social security number, address, salary, gender, and birth date. An employee is assigned to
one department but may work on several projects, which are not necessarily controlled by the same
department. They keep track of the number of hours per week that an employee works on each
project. Also, each employee will be monitored by performance supervisor who will monitor
his/her results and achievements.
For the company requirements above, please create an ERD to show how you would track this
information using Microsoft Visio Pro or any other applications for drawing. Show entity names,
primary keys, attributes for each entity, relationships between the entities and cardinality.
Practice task 5: PostgreSQL installation
Students should independently install PostgreSQL (open source) for further mastering the
database programming.

1. https://www.postgresql.org/download/macosx/ - MAC users
2. https://www.postgresql.org/download/windows/ - Windows users