Information Science

1. Use the following business rules to create a Crow’s Foot ERD. Write all appropriate connectivities and cardinalities in the ERD. (20 points)

1) A department employs many employees, but each employee is employed by only one department.

2) Some employees, known as “rovers,” are not assigned to any department.

3) A division operates many departments, but each department is operated by only one division.

4) An employee may be assigned many projects, and a project may have many employees assigned to it.

5) A project must have at least one employee assigned to it.

6) One of the employees manages each department, and each department is managed by only one employee.

7) One of the employees runs each division, and each division is run by only one employee.

2. Create an ERD in Crow’s Foot notion that can be implemented in the relational model to represent and manage cities, counties and states in the US using the following description of attributes and assumptions: (20 points)

The minimum attributes to be recorded for cities, counties and states are:

1) For states: name, population and state capital (a city) 2) For counties: name, population and the located state.

3) For cities: name, population, the located state and the located county There are some uniqueness assumptions:

1) Names of states are unique

2) Name of counties are unique within a state

3) Cities are unique only within a state

4) Some counties and cities have the same name, even within a state

5) All cities are located within a single county

3. Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure below into a dependency diagram that is in at least 3NF. (20 points)

4. The figure below shows the database structure and a few sample data for such a database. Specify the following queries in SQL on the database schema of this figure. (20 points)

Figure: A database that stores student

and course information

1) Retrieve the names of all senior students (Class =4) majoring in ‘CS’ (computer science).

2) Retrieve the names of all courses taught by Professor King in 2007 and 2008.

3) Insert a new student <’Johnson’, 25, 1, ‘MATH’> in the database.

4) Change the class of student ‘Smith’ to 2.

5) Delete the record for the student whose name is ‘Smith’ and student number is 17.

5. From the 26 Geodatabase topology rules, select and explain six (6) rules, including the following components in your explanation: 1) the feature class(es) the rule applies to, 2) the spatial relationship it protects, 3) a generalized description of when to use the rule, 4) an example (your own example, do not use the one included in the rule diagram) that illustrate a real-world application of the rule and 5) a real world example (your own example) that violate this rule. These six rules must include the automatic rule, 2 polygon rules, 2 line rules and 1 point rule. (20 points)