Database Management Final Practice 1
Q1. Consider the following set of simplified requirements for a System-info database that is used to keep
track of the usages of (software) systems in a company.
(i) Each department has a unique name and a unique number. A department may use several systems.
The database keeps track of the number of hours per week that a department uses each system.
(ii) Each system has a unique name, a unique number, and a particular administrator who manages the
system. A system may be used by a number of departments.
(iii) The database stores each administrator’s name, unique identifier and e-mail address. Each
administrator must manage at least one system.
(a) (8%) Draw an ER (Entity-Relationship) schema diagram for this application. You need to clearly
indicate the cardinality ratio (1:1, 1:N, or M:N) and participation constraints (total or partial) of each
relationship. (State clearly any additional assumptions you make)
(b) (6%) Map the ER schema into the corresponding relational database schema diagram. Specify all
primary keys and foreign keys.
Suppose that it is also necessary to keep track of different types of systems (Internet systems and Intranet
systems), in which an Internet system may serve several customers (cooperative companies). Each
customer has a unique name, a unique identifier, and an address. A customer may be served by several
Internet systems. Notably, a system may be a member of Internet systems, Intranet systems or both. Every
