Advanced Database Course (ESED5204)

1. University of Palestine Software Engineering Department Advanced Database Course (ESED5204) Eng. Hanan Alyazji

2. Database Design

3. Project Phases • Planning • Why build the system? How should the team go about building it? • Analysis • Who uses system, what will it do, where and when will the system be used? • Design • How will the system work? • Implementation • System delivery.

4. Project Phases

5. Planning • Identifying business value. • Analyze feasibility. • Develop work plan. • Staff the project. • Control and direct project.

6. Analysis • Analysis strategy. • Gathering business requirements. • Requirements definition use cases. • Process modeling. • Data modeling.

7. Design • Design selection. • Architecture design. • Interface design. • Data storage design. • Program design.

8. Implementation • Construction • Program building. • Program and system testing • Installation • Conversion strategy. • Training plan. • Support plan.

9. A Data Modeling Process Steps in the data modeling process • Plan project. • Determine requirements. • Specify entities. • Specify relationships. • Determine identifiers. • Specify attributes. • Specify domains. • Validate model.

10. 1. Planning the Project: • Obtaining project authorization and budget. • Building the project team. • Planning the team’s activities. • Establishing tools, techniques, and standards for consistent results. • Defining the project’s target.

11. 2.Determining System Requirements: • Sources for data modeling requirements. • User interviews and user activity observations. • Existing forms and reports. • New forms and reports. • Existing manual files. • Existing computer files/databases. • Formally defined interfaces (XML). • The result of the requirements determination will be a repository of notes, diagram, forms reports, and files that can be used to develop the data model.

12. 3. Specifying Entities: • An entity is something that the users want to track; something the users want to keep data about. • Entities • Can be physical things or logical concepts. • Are identifiable; you can tell one from another. • Are things described by nouns, not characteristics described by adjectives.

13. 4. Specifying Relationships: • Includes: • Relationship type. • Name of the relationships. • The name of the relation is followed (in parentheses) by the names of the attributes of that relation, e.g.: Student (Id, Name, Dept, DOB)

14. Entity Set -> Relation Relation: student (Id, name) Id name Student

15. Employee Flight Emp No Flight Id Flight No Date 5. Determining Identifiers: • Identifier is an attribute or group of attributes that uniquely identifies an entity instance.

16. First Name Mid Initials Name Last Name 6. Specifying Attributes and Domains: • Find attributes on forms, reports, existing files, and add them to entities. Emp No

17. 7. Validating Model: • Data model is a model of humans’ models, not a model of reality. • A data model is wrong if it does not accurately reflect the ways the users think about their world. • Data models are validated through a series of reviews • Normally, a team review is followed by user reviews. • E-R model as well as prototypes of forms and reports may be used to communicate to users features of the data model.

18. Database Design Conceptual design Logical design Physical design

19. Example • A Company is organised into departments. • Each department has a number and an employee who manages the department. • We keep track of the start date when that employee started managing the department. • A department may have several locations. • A department controls a number of projects. • Each of which has a name, a number and a single location.

20. We store each employee’s name, Id number, address, salary, 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. • We keep track of the number of hours per week that an employee works on each project. • We also keep track of the direct supervisor of each employee. • We keep track of the dependants of each employee. • We keep each dependant’s name, birth date and relationship to the employee. • Phase 1 of database design process.. (i.e. Requirements Collection and Analysis )

21. Conceptual Design All the requirements collected at Phase 1 are analysed to create a Conceptual Schema. This process is called the Conceptual Design. We identify the entities, their attributes, relationships and constraints(business rules). The conceptual schema is used as a reference to ensure that all user’s data requirements are met and the requirements do not include any conflicts.

22. Conceptual Design Entities Department Employee Project Dependent

23. Dept No Dept Name Department Location Phone Employees Detailed Conceptual Design Dept No unique identifier of a dept. Identifier Dept Name nameof a department Unique Location location of a department Multi-valued Phone phone no. of a department Employees no. of employees in a dept. Derived

24. Proj No Project Proj Name Location Detailed Conceptual Design Project Name Number Location Department Control Leader  Employee Proj No unique identifier of a project Identifier Proj Name name of a project Unique Location location of a project

25. Detailed Conceptual Design Employee Name National ID Address Salary Birth Date Works for Department Supervise Employee Emp No

26. Detailed Conceptual Design Employee Emp No unique identifier of an emp. Identifier Emp Name name of an employee First Name first name of an employee Mid Initials middle initials of an employee Last Name last name of an employee NID national id of an employee Unique Address address of an employee Salary salary of an employee Gender sex of an employee DOB birth date of an employee

27. First Name Emp No Mid Initials Emp Name Employee Last Name NID Address Salary Gender DOB Detailed Conceptual Design

28. Depd Name Gender Dependent DOB Relation Detailed Conceptual Design Dependent Name Birth Date Relationship Depd Name name of a dependent Part of Key Gender sexof a dependent DOB birth date of a dependent Relation relationship of a dependent to an employee

29. Conceptual Design Relationships A Department has Many Employees An Employee works for A Department

30. one to many relationship Department Personnel Sales Employee C A E B D A C B F E

31. Conceptual Design Relationships A Department has A Manager (Employee) An Employee manage A Department

32. one to one relationship Department Personnel Sales Manager (Employee) B D

33. Conceptual Design Relationships A Department controls Many Projects A Project controlled by A Department

34. Conceptual Design Relationships An Employee works on Many Projects A Project has Many Employees

35. many to many relationship Project Delivery Construction Employee D B C

36. Conceptual Design Relationships An Employee supervised by An Employee An Employee supervise Many Employees

37. Conceptual Design Relationships An Employee has Many Dependants A Dependant belongs to An Employee

38. Conceptual Design Entities / Relationships?? A Company has Many Departments A Department has Many Locations

39. Conceptual Design Notations Entity Relationship Attribute

40. DEPARTMENT MANAGES EMPLOYEE WORKS_ON PROJECT

41. One to One director Company Employee Existence Conditions D ABC Ltd. B C

42. One to Many director Company Employee Existence Conditions D ABC Ltd. B C

43. Many to Many director Company Employee Existence Conditions XYZ Ltd. D B C ABC Ltd.

44. supervise works Department Employee manage has control works on Dependent Project

45. Exercise (Conceptual design) Create a conceptual E.R model of the database for the following lists. (List up the necessary DATA ITEMS, set up ENTITIES and their ATTRIBUTES, and identify the relationship among the entities ) List 1 Track No: 1 Track name: Managing information using Database Participant name Participant code Age Position Country Address List 1 is the list of participants’ information by track List 2 Country code Country name Participant code Participant name Track name List 2 is the list of participants’ information by countries

46. Answer Just one One or many Zero one or many Just one

47. Exercise: Primary and Foreign Key Please identify primary and foreign key. Participant code Participant name Age Position Address Country code Participant code Track code Track name Country code Country name Primary key Foreign key

48. Answer Participant code Participant name Age Position Address Country code Participant code Track code Track name Country code Country name

49. Logical Design • For each table, need to define: • Name of the table; • List of simple columns in brackets; • PK and, where appropriate, and FKs. • Referential integrity constraints for any FKs identified. • For each column, need to define: • Its domain, consisting of a data type, length, and any constraints on the domain; • An optional default value for the column; • Whether the column can hold nulls.

50. Integrity constraints • These help maintain the accuracy and integrity of the data in the database. • Domain Constraints - a domain is the set of allowable values for an attribute. • Domain definition usually consists of 4 components: domain name, meaning, data type, size (or length), allowable values/allowable range (if applicable). • Entity Integrity ensures that every relation has a primary key, and that all the data values for that primary key are valid. No primary key attribute may be null.