Conceptual and Logical Design
(a) Draw an ER diagram according to the list of requirements given below.
(b) Convert your ER diagram to a relational database schema.
Deliverables: ER diagram and relational schema. Hand-written scanned documents will NOT be accepted, please use the drawing tools(Dia Diagram Editor).
Design a code-versioning system (CVS) database for a software development company based on the following requirements.
• Every user has a name, multiple e-mails and a unique username.
• Every software project developed by the company is referred to with a unique name and
version combination. Together with this information, the following data of a project
(a) a textual description of the project and,
(b) the root directory (that contains documents related with the project).
• Documents are identified by their absolute path.
• Every document has exactly one owner, who is a user. Document ownership is established upon document creation and the timestamp of this event is kept in the database
explicitly. Ownership can later be transferred but the “date created” property of a
document cannot be modified.
• The database does not keep any history of document ownership. Only the current
owner information is maintained.
• Every project contains at least one document. However some documents may not be
contained in any project, such as personal files.
• Users contribute to multiple projects. For each user working on a project, the user’s
contribution start-date and end-date are stored. Assume that once a user stops working
for a project, he/she cannot work for the same project again. Therefore, there is only
one start-date for each user working on each project.
• The database also keeps the duration of time each user worked on a project. Notice
that this value can b e calculated easily based on the start-date and end-date fields
• Every project is coordinated by a user. A co ordinator may co ordinate multiple projects
• The most important part of the database contains access logs of users. Whenever a
user accesses a document, an access log entry is created by the system.
• Each access log entry contains the following information:
(a) type of access: either ‘R’ for read, or ‘W’ for write, ‘RW’ for read and write,
(b) event timestamp: the date and time when the user accessed the document, and,
(c) a field called “difference”.
• The field “difference” is textual (i.e., is a string) and contains all the details needed
for undoing an edit operation by a user. Think of this field as an incremental backup
of the document.
• Over time, it is natural that a user will have multiple access log entries for a document.
• However, a user cannot have two access log entries on the same document with the
Do not assign auto-increment type of keys to any of the entities.