Question

Create ER Model

1. Assets are resources that are used in CMS to display content. Each asset is either an image or an article. An asset has a description and creation date; Pictures have picture dates, articles have a title
2. Articles contain sections. (With these sections, articles can be broken down into a main article and subsequent pages, for example.) Sections are used by exactly one article and numbered within one article. Sections contain the text. (So if you want to create a single website in the CMS, you create an asset of the "Article" type with the heading and a section for this article that contains the text. For articles that are to be displayed on multiple HTML pages, an article asset is created with the heading and several sections, each containing the text of the individual pages)
3. Users are saved in the CMS with their name, a login and their password
4. Editors are users who can edit assets. For editors, in addition to their attributes as users, their personnel numbers are also saved (including accounting reasons)
5. Each user is a member of at least one group
6. Each group has name
7. Groups can have any number of subgroups, but only belong to one supergroup. (An example is a "editors" group that has subgroups "sports editors" and "business editors")
8. To manage rights, there are roles that have a name and one or more privileges - e.g. "Edit pictures" or "Create user". (These privileges are defined in the application program that uses this database, so the privileges should not be modeled as an entity.)
9. Groups can have one or more roles. Roles can belong to one or more groups

Answer 1

Please follow the below steps to get ERD for given system:

Step 1: Identify the list of entities in the CMS

• Asset
• Asset -> Image : Sub entity to Asset
• Asset -> Article : Sub entity to Asset
• Section: Weak entity as Section_id will be unique within the Article
• User
• User -> Editor a subtype of entity User
• Group
• Subgroup
• Role

Step 2: List the possible and required attributes for the entities in the CMS system:

***Unique attributes are underlined and highlighted

• Asset(Asset_Id,Description, Date); added Asset_id to uniquely identify the records
• Asset -> Image (Image_Date)
• Asset -> Article (Title)
• Section(Section_id, text); Secton_Id is partial key with Article’s Asset_Id
• User(Login ,Username, Password)
• User -> Editor(Phone_number)
• Group(Name)
• Subgroup(SubGroupName)
• Role(Name, Privileges)

Step 3: List out the relationship between the entities in the system along with the cardinality:

• Asset can be either Image or Article(Subtype and Supertype hierarchy)
• Article contains many Sections whereas a Section can belong to one Article(One to Many relationship)
• Article are edited by Editor (Cardinality not specified in description)
• User is a member of one or many Group whereas a Group can belong to many users.(Many to Many relationship)
• Group has Many Subgroups whereas a Subgroup will have one Group (One to Many relationship)
• Group has many Roles whereas a Role can belong to many Group (Many to Many relationship)

Step 4: Draw the ER Diagram for the identified entities, attributes and relationships:

Step 5: Notations used in the diagram

Entity:

Relationship:

Attribute:

         

Primary Key Attribute:

Partial Key attribute(dotted underlined):