This is an introduction to database question which needs deep understanding of concepts

Analyze the business rules of a Wall Coverings Company carefully and use it to answer the given questions.The company has three stores that sell wallpaper and accessories. Customers place orders through a branch. The owner would like to track the following about customers: Name, Address, City, State, Zip Code, Telephone, Date of Birth, and Primary Language. A customer may place many orders. A customer does not always have to order through the same branch all the time. Customers may have one or more accounts, although they may also have no accounts. The following information needs to be recorded about accounts: Balance, Last payment date, Last payment amount and Type. A branch may have many customers. The following information about each branch needs to be recorded: Branch Number, Location (Street, City, State, Post Code), and Square Footage (area). A branch may sell all items or may only sell certain items. Orders are composed of one or more items. The following information about each order needs to be recorded: Order Date and Credit Authorization Status. Items may be sold by one or more branches. The company wishes to record the following about each item: Description, Color, Size, Pattern, and Type. An item can be composed of multiple items; for example, a dining room wall covering set (item id 20) may consist of wallpaper (item id 22) and borders (item id 23). The company employs 56 employees and would like to track the following information about employees: Name, Address (Street, City, State and Postcode), Telephone, Date of Hire, Title, Salary, Skill, and Age. Each employee works in one and only one branch. Each employee may have one or more dependents. The company'wishes to record the name of the dependent as well as the age and relationship.

(a)Based upon the provided information, draw an Entity-Relationship (ER) Diagram using the Crow's foot model symbols and include all attributes. (b)In the ER diagram: (i) Identify the main relationship types (connectivity) between the entities. (ii) Determine the multiplicity constraints (cardinality) for each relationship. (c)Determine primary key and foreign key attributes for each entity. (d)Resolve all many-to-many relationships into one-to-many relationships. (e)Using SQL statements, create a table for the EMPLOYEE entity. Specify all constraints, if any.

marks Analyse the business rule of Royal Engineering Incorporated carefully and use it to answer the provided questions The Royal Engineering Incorporated, is an engineering firm with approximately 500 employees. A database is required to keep track of all employees, their skills, projects assigned and departments worked in Every employee has a unique number assigned by the fim, required to store his or her name and date of birth If an employee is currently married to another employee of Royal Engineering the date of marriage and who is married to whom must be stored. Each employee is given a job title (for example, engineer, secretary, and so on). An employee does only one type of job at any given time, and we only need to retain information for an employee's current job. EXAMINERS: GEORGE KODJO ANNI AND ISAACK. NTI Page 1 of 4 The.linhas a number of different departments, each with unique name. An employee pear fort to only one department. Each department has a phone number. To produce Various kinds of equipment, each department deals with many vendors. A vender typically supplies equipment to many departments. We are required to store the name fand dress of fich vendor and the date of the last meeting between a department and a Vendor ..Many employees can work on a project. An employee can work on many projects. (For example, Massey Refinery, Hamilton Petrochemicals, and so on) but can only be assigned to almost one project in a given period of time. An employee can have many skills (preparing material requisition, checking drawings, and so on), but she or he may use only a given set of skills on a particular project. (For example, an employee KENNETH may prepare requisition for Massey Refinery project and prepare requisition as well as check drawings for Hamilton Petrochemicals) Employees we each skill that they possess in at least one project. Each skill is assigned a number, and we must store a short description of each skill. Projects are distinguished by project numbers, and we must store the estimated cost of each project Based upon the provided information, draw an Entity Relationship (ER) Diagram using the Crow's foot model symbols and include all attributes mark b. In the ER diagram () Identify the main relationship types (connectivity) between the entities (1) Determine the multiplicity constraints (cardinality) for each relationship 4 markal 6. Determine primary key and foreign key attributes for each entity 14 marks d. Resolve all many to many relationships into one-to-many relationships Using SQL statements, create a table for the EMPLOYEE entity. Specify all constraints, if any marks Analyse the business rule of Royal Engineering Incorporated carefully and use it to answer the provided questions The Royal Engineering Incorporated, is an engineering firm with approximately 500 employees. A database is required to keep track of all employees, their skills, projects assigned and departments worked in Every employee has a unique number assigned by the fim, required to store his or her name and date of birth If an employee is currently married to another employee of Royal Engineering the date of marriage and who is married to whom must be stored. Each employee is given a job title (for example, engineer, secretary, and so on). An employee does only one type of job at any given time, and we only need to retain information for an employee's current job. EXAMINERS: GEORGE KODJO ANNI AND ISAACK. NTI Page 1 of 4 The.linhas a number of different departments, each with unique name. An employee pear fort to only one department. Each department has a phone number. To produce Various kinds of equipment, each department deals with many vendors. A vender typically supplies equipment to many departments. We are required to store the name fand dress of fich vendor and the date of the last meeting between a department and a Vendor ..Many employees can work on a project. An employee can work on many projects. (For example, Massey Refinery, Hamilton Petrochemicals, and so on) but can only be assigned to almost one project in a given period of time. An employee can have many skills (preparing material requisition, checking drawings, and so on), but she or he may use only a given set of skills on a particular project. (For example, an employee KENNETH may prepare requisition for Massey Refinery project and prepare requisition as well as check drawings for Hamilton Petrochemicals) Employees we each skill that they possess in at least one project. Each skill is assigned a number, and we must store a short description of each skill. Projects are distinguished by project numbers, and we must store the estimated cost of each project Based upon the provided information, draw an Entity Relationship (ER) Diagram using the Crow's foot model symbols and include all attributes mark b. In the ER diagram () Identify the main relationship types (connectivity) between the entities (1) Determine the multiplicity constraints (cardinality) for each relationship 4 markal 6. Determine primary key and foreign key attributes for each entity 14 marks d. Resolve all many to many relationships into one-to-many relationships Using SQL statements, create a table for the EMPLOYEE entity. Specify all constraints, if any