database design and development Final assignment, Assignments of Database Management Systems (DBMS)

Download database design and development Final assignment and more Assignments Database Management Systems (DBMS) in PDF only on Docsity!

TELECALL DATABASE

SYSTEM

Contents

• 1- Introduction:...........................................................................................................................................
  • 1-1 Scenario and Requirements:.............................................................................................................. - 1-1-1 Telecall Scenario:........................................................................................................................ - 1-1-2 Requirements:............................................................................................................................
  • 1-2 User Groups and System needs:........................................................................................................
• 2- Database Design:.....................................................................................................................................
  • 2-1 Conceptual Design:............................................................................................................................
  • 2-2 Logical Model:................................................................................................................................... - 2-2-1 Schema:..................................................................................................................................... - 2-2-2 Normalization:............................................................................................................................ - 2-2-3 Mapping:.................................................................................................................................. - 2-2-4 Logical Design:..........................................................................................................................
  • 2-3 Physical Model:................................................................................................................................
• 3- Technical Documentation and Development:....................................................................................... - 3-1 Flow charts and Data Movement Diagram:....................................................................................
  • 3-2 Installation and Using of XAMPP and phpMyAdmin:.......................................................................
  • 3-3 DDL & DML:.....................................................................................................................................
  • 3-4 DCL:.................................................................................................................................................
  • 3-5 Database Maintenance:...................................................................................................................
  • 3-7 GUI Development:...........................................................................................................................
• 4- User Documntation:..............................................................................................................................
  • 4-1 Officers Training...............................................................................................................................
  • 4-2 Branch Managers Training:................................................................................................................
  • 4-3 Employees Training:..........................................................................................................................
  • 4-4 Customers Training:...........................................................................................................................
• 5- Testing:.................................................................................................................................................... - 5-1 Data Validation:................................................................................................................................
  • 5-2 Output Validation:.............................................................................................................................
  • 5-3 Security Validation:............................................................................................................................
  • 5-4 GUI’s Validation:................................................................................................................................
• 6 - Assessment:..........................................................................................................................................

1-1-2 Requirements:

1- Each Employee in the company has a name (first name and last name), an SSN, date of birth, a salary, an email, branch name, and password for log in. 2- Each employee has a position in this company (CEO, COO, CFO, Branch Manager, department manager, HR department employee, Sales department employee, Accounting department employee, Customer Service department employee), a position may be assigned to multiple employees while employee has only one position. The system should store an ID and the names of these positions. 3- Each Branch has a name, phone number, address (City, street name). 4- Each Department has a name and phone number. 5- Each Department can have multiple employees while the employee works only for one department. There is no department which has no employees. 6- Each subscription has a subscription number, date of purchase.

7- The subscription has six types (Fiber 50Mbps, Fiber 100Mbps, 4G Home 1000G, 4G Home

2000G, Telecall 8, and Telecall 10) Each subscription has only one type while the type can be

assigned for multiple subscriptions. Each subscription type has(ID, Name, Monthly fee, Commitment Duration, Speed, Internet Bundle, Minutes, SMS). (In case a new type of subscription was added it will have no subscriptions). 8- The system should store these information for each customer: a name (First name, Middle name, Last name), some of ID card information (National number, ID number, Date of Birth), an email, a Phone Number, and multiple addresses. 9- Each customer can have multiple subscription types and multiple subscriptions in each type while the subscription must be assigned to one customer. 10- Each subscription may have more than one bill or no bills.

11- Customers can pay their bills at the branch by the customer service employee, the employee can accept the payment for multiple bills, but each bill assigned to one employee. 12- The system should store these information for each bill: ID, bill value, and the issuance date of the bill.

1-2 User Groups and System needs:

 Board of Officers:

1- View/Add/Delete the branches and its managers.

2- View/Add/delete subscription types.

3- View the most selling subscription type.

4- View employees information.

 Branch Manager:

1- Add or delete employees. (In his branch)

2- View employees information. (in his branch)

3- View Customers information.

4- View Customer’s subscription.

5- View departments phones numbers in each branch along with branch name.

2- Database Design:

2-1 Conceptual Design:

This is the first step in database design process, we clarify the entity types and

the relationships between them.

In this graph, each employee has a position and works in a one department in one branch and the branch has multiple departments and the department has multiple employees. Each Customer has a one subscription or more and each subscription has a one bill or multiple bills, also each subscription has one subscription type.

2-2 Logical Model:

2-2-1 Schema:

Employee (SSN, Name (first name, last name), Date of Birth, Salary, Email, Password, branch name, Position ID, and Name of the Position). Branch (Name, Phone Number, Address (City, street name)). Department (Name, Phone Number). Customer (National Number, ID number, Date Of birth, Name (First Name, Middle Name, Last Name), Email, Phone Number, Multiple Addresses (City, Neighborhood, Street Name, Building Number). Subscription (Number, Date of Purchase, bill ID, bill value, bill issuance date). Subscription type (ID, Name of the type, Monthly fee, Commitment Duration, Speed, Internet Bundle, Minutes, SMS).

Employee (SSN, Name (first name, last name), Date of Birth, Salary, Email, Password, branch name, Position ID, and Name of the Position). After First Normal Form: Employee (SSN, first name, last name, Date of Birth, Salary, Email, Password, branch name, Position ID, and Name of the Position). Example2: Before First Normal Form: Branch (Name, Phone Number, Address (City, street name)). After First Normal Form: Branch (Name, Phone Number, City, street name)). Example3: Before First Normal Form: Customer (National Number, ID number, Date Of birth, Name (First Name, Middle Name, Last Name), Email, Phone Number, Multiple Addresses (City, Neighborhood, Street Name, Building Number). After First Normal Form:

Customer (National Number, ID number, Date Of birth, First Name, Middle Name, Last Name, Email, Phone Number) Customer Addresses ( Customer national Number, City, Neighborhood, Street Name, Building Number)  Second Normal Form: By this form we can get rid of the multiple columns of the same kind of data in some records and this happens the primary key is composed of multiple keys and there are non-prime attributes which are partially dependent on key. To solve this problem, we should remove the partial dependencies by removing the partially dependent attributes and put them in a new relation along with a copy of their key. Example1: Before Second Normal Form: Subscription (Number, Date of Purchase, bill ID, bill value, bill issuance date). After Second Normal Form:

Employee (SSN, first name, last name, Date of Birth, Salary, Email, Password, branch name). Position (ID, Name of the Position)

2-2-3 Mapping:

It is the process of converting the ER model to Relational models to implement it directly by RDBMS (MySQL, Oracle, IBM DB2….). 1) Mapping 1:1 Relation Types: We can map between two 1:1 relations by the Foreign Key Approach, and that can be done by including the Primary key of the first relation as a foreign key into the second relation. 2) Mapping 1:M Relation Types: And that can be done by including the primary key of the (1) side relation as a foreign key into the (M) side relation.

3) Mapping M:N Relationship Types: it can be solved by creating a new relation and include the first’s relations primary key as a primary and foreign key into it, also we should include the second’s relation primary key as a primary and foreign key into the new relation.

2-2-4 Logical Design:

Here is Telecall Logical Design according to the mapping with clarification of the relationships between these tables, and it is the foundation of the Physical Design.

2-3 Physical Model:

It clarifies the data types of the attributes in the logical Design tables, and the next step after these designs is to implement them in Database Management System.

The Employee should open the System by URL, a Main page will appear, he should choose Employee Link, then a sign in form will appear to enter his Email and password, if Email or password or both of them wrong then will not be an authorized User, if not, a home page will appear with his/her information and Links to other pages (Customers, Subscriptions, Bills, Customer Addresses, Departments, Log out). Branch Departments

The Customer should open the System by URL, a Main page will appear, he should choose Customer Link, then a sign in form will appear to enter his Email and password, if Email or password or both of them wrong then will not be an authorized User, if not, a home page will appear with his/her information and Links to other pages (Subscriptions, Addresses, Bills, Log out). Employee