INF3703 Assignment 2 (COMPLETE ANSWERS) 2025 - DUE 27 June 2025

INF3703 Assignment 2 (COMPLETE ANSWERS) 2025 - DUE 27 June 2025 ;100 % TRUSTED workings, explanations and solutions. For assistance call or W.h.a.t.s.a.p.p us on ...(.+.2.5.4.7.7.9.5.4.0.1.3.2)........... Question 1 [30] As stated in the case, Skyworth follows the Systems Development Life Cycle (SDLC) (see page 529 of Coronel et al., 2020) to design information systems for their clients. In the planning phase, the database design and development team conducted a feasibility study to address, amongst others, the technical aspects of hardware and software requirements (see page 530 Coronel et al., 2020). The database architect3 , Sanjay Kumar, promoted the Oracle Database 21c Express Edition and SQL Developer Tool to be deployed to design the database for the POS project. Sanjay instructed you to install and prep Oracle 21C for the physical design by performing the required configurations and troubleshooting. 1.1. You installed the Oracle Database 21c Express Edition for Windows x64 and the SQL Developer tool. 1.1.1. Using SQL Plus, connect the SYS administrator account to the container database. Capture a screenshot showing only the necessary details, such as connection message and relevant database information. For example: (5) 1.1.2. In SQL Developer, create a new connection profile for the SYS account and name it ASS2-ADMIN_CDB4_STUDENT_NUMBER (e.g., ASS2_ADMIN_CDB_). Capture a screenshot of the NEW/SELECT DATABASE CONNECTION dialog box indicating connection STATUS : SUCCESS. (5) 1.2. In SQL Developer, create a new connection profile for the SYSTEM administrator account connecting to the pluggable database and name it ASS2_ADMIN_PDB_STUDENT NUMBER (e.g., ASS2_ADMIN_PDB5_). Create a screenshot of the NEW/SELECT DATABASE CONNECTION dialog box indicating connection STATUS : SUCCESS. (1) 1.3. In SQL Developer, connect to ASS2-ADMIN_CDB-STUDENT NUMBER. Once connected, follow these steps to change the password of the SYSTEM user: (4) 3 The database architect designs and implements database environments (conceptual, logical, and physical). To fulfil the role of the database architect, one requires the skills of DBMS fundamentals, data modelling, SQL and hardware knowledge. See page 29 of Coronel et al., (2020). 4 CDB denotes ‘container database’. 5 PDB denotes ‘pluggable database’. 2 INF3703 – ASSIGNMENT 2 • Click the plus icon located to the left of the ASS2_ADMIN_CDB-STUDENT NUMBER connection to expand the container content and view the available objects within the container. Screenshot 1: Click the plus icon located before Ass-Admin_CDB_student_number to expand the tree structure. • Locate OTHER USERS at the end of the tree structure, then click the plus icon to expand the container content. • Locate SYSTEM, right-click and select EDIT USER. • In the EDIT USER dialog box, specify your student number as the new password. 1.3.1. In SQL Plus, connect to the SYSTEM account using your new password. Capture a screenshot of the SQL Plus window illustrating the SYSTEM user successfully connected to the database with new password. 1.4. Create a new tablespace6 In SQL Developer (5) 1.4.1. Assign the following details In the CREATE TABLESPACE dialog box: • Name: TABLESPACE_STUDENT NUMBER. For example, TABLESPACE_ • Tablespace Type: Permanent 1.4.2. Assign the following details in FILE SPECIFICATIONS tab: • Name: “TABLESPACE_STUDENT NUMBER.DBF”, e.g., “TABLESPACE_.DBF” • Directory: C:appAsusproduct21cdbhomeXEdemo. Note: the path will be slightly different on your PC/laptop. • File size: 50M • Checkmark Auto Extent On • Next Size: 10M • Max Size: 100M 1.4.3. Capture a screenshot of the TABLESPACE tab displaying the information of TABLESPACE_STUDENT NUMBER. For example: 6 Watch INF3703 Webinar_The Oracle (Lesson 2).mp4 in which I demonstrate how to create a new tablespace. 3 INF3703 – ASSIGNMENT 2 1.5. In SQL Developer, connect to ASS2_ADMIN_PDB_STUDENT NUMBER and create a new user7 . (5) 1.5.1. Assign the following details in the CREATE USER dialog box: • User Name: ASS2_STUDENT NUMBER, e.g., ASS2_ • New Password: Student number, e.g., • Default Tablespace: TABLESPACE_STUDENT NUMBER • Temporary Tablespace: TEMP 1.5.2. In SQL Plus, connect to the SYSTEM user and write the SQL syntax that shows/verifies that ASS-2-STUDENT NUMBER is allocated to TABLESPACE_STUDENT NUMBER. Capture a screenshot. 1.6. In SQL Developer, capture a new connection profile for ASS2_STUDENT NUMBER. Capture a screenshot of the NEW/SELECT DATABASE CONNECTION dialog box showing connection STATUS : SUCCESS. (5) End of Question 1 Total: 30 Question 2 [50] As stated in the case, Skyworth follows the DBLC to guide the design of the database. Currently, the database team is engaged in the conceptual design stage. During this stage, data modelling is utilised to create an abstract database structure that accurately represents the real-world objects. The conceptual model must reflect a clear understanding of the business and its functional area. Sanjay assigns various data modelling tasks to you, which can be completed using drawings or drawing software. It is important to note that the models generated during the conceptual design stage are hardware and software independent. 2.1. At the Table Mountain Shopping Centre located in Cape Town CBD, Siya Khumalo and Deacon Willemse are Heads of Maintenance (HOM). Their duties include managing the parking attendance, the car wash and general maintenance and upkeep of the shopping centre premises and infrastructure. Siya and Deacon will be responsible for issuing the POS devices to parking lot staff before their shift starts. This process is defined by the following business rule: An HOM may check out many POSs, and a POS may be checked out by many parking area staff. Additionally, the following observations inform the business rules: a parking area employee is not required to check out a POS device because parking area employees work in shifts, and a POS device does not need to be checked out to exist in inventory. After consulting with your fellow database administrators, you observe that the business rule is best captured in a composite entity relationship diagram (ERD). For an example, see Figure 11.2 on page 585 of Coronel et al. (2020). 2.1.1. Using entity modelling, define three separate (not linked) entities to graphically illustrate the business rules. Add the underscore character followed by your student number to each entity name. For example, TABLENAME_. (3 x 2) = (6) 2.1.2. Provide a brief description of the role of each entity. (3 x 2) = (6) 2.1.3. Using entity modelling, define primary keys (PK) and foreign keys (FK) for… • Entity 1; (2) • entity 2; and the (2) • the bridge entity (2 x 2) = (4) 2.2. The tables illustrated below – WASH_INV_GENERATOR_STUDENT NUMBER, CARWASH_SERVICE_STUDENT NUMBER, INVOICE_STUDENT NUMBER, and VEHICLE_REG_STUDENT NUMBER – are based on the shopping mall’s car cleaning service. Note the following: although the car cleaning service exists, patrons are not required to use it. Conversely, some patrons regularly make use of the cleaning service. 5 INF3703 – ASSIGNMENT 2 2.2.1. Mariaan De Wet, the database designer with more than 10 years’ experience in database design, deployed the relational model (see page 41 Coronel et al., 2020) to conceptualise the tables. Sanjay instructs you to convert the tables into corresponding business rules for documentation purposes. Examine each table’s attributes, records, primary key allocation, and foreign key allocation. Formulate the business rules that underpin the relationships between the tables. (3 x 4) = (12) 6 INF3703 – ASSIGNMENT 2 2.2.2. Create an ERD that includes multiplicities, association names, and association directions to illustrate the connections between the entities. (9 x 2) = (18) End of Question 2 Total: 50 7 INF3703 – ASSIGNMENT 2 Question 3 [50] Following the conceptual phase, the database team engaged with the logical database design phase. The aim of the logical design stage is to map the conceptual model into a logical model that can then be implemented on a relational DBMS. Not all database elements can be modelled and must be enforced at application8 level (the logical design phase). For example, the constraint ‘a parking attendant must return a POS device after 5 hours, when a shift ends’ cannot be reflected in an ERD. See Table 11.5 on page 601 of Coronel et al. (2020) for more information about integrity constraints. 3.1. Step 1 of the logical design phase involves creating relations for strong entities. A strong entity (see page 117 of Coronel et al., 2020) refers to a table that can be uniquely identified by its attributes without requiring any relationship with other entities. Therefore, a strong entity can exist on its own and is independent. 3.1.1. Examine the POS_Device entity as illustrated below. Formulate the POS_DEVICE_STUDENT NUMBER relation. (2) 3.2. Step 2 of the logical design phase involves creating relations for weak entities. A weak entity (see page 181 of Coronel et al., 2020) refers to an entity that is existence dependent; that is, it cannot exist without the entity with which it has a relationship. 3.2.1. Examine the ERD below, illustrating the relationship between the entities POS_Device and POS_Checkout. Formulate the relation for the weak entity. (4) POS_DEVICE_STUDENT NUMBER POS_CHECKOUT_STUDENT NUMBER Device_ID {PK} Is checked out in out in ► Shift_ID 9 {PK} Manufacturer Device_ID {FK} 8 The term ‘application’ in the context of the logical design phase derives from the verb ‘apply’ and should not be confused with application software. 9 The PK attribute Shift_ID derives from an entity called SHIFT_ALLOCATION_STUDENT NUMBER. Shift_ID determines the following values in SHIFT_ALLOCATION_STUDENT NUMBER: Shift_ID → Employee_ID, Designation, Shift_Date, Shift_Start_Time, Shift_End_Time. The Designation domain comprises ‘parking attendant service’ and ‘car cleaning service’. 8 INF3703 – ASSIGNMENT 2 3.2.2. Define SHIFT_ALLOCATION_STUDENT NUMBER in table format. The table must only contain two rows to illustrate that an employee can work two shifts on one day. Specify the table name, the primary key, and the foreign key. (3 x 1) + (3 x 3) = (12) 3.3. Step 3 of the logical design phase involves mapping multivalued attributes (see page 596- 597 of Coronel et al., 2020). Examine the entity titled PARKING_LOT_EMPLOYEES_STUDENT NUMBER as illustrated in the graphic below. 3.3.1. Which attribute is the multivalued attribute? Motivate your answer. (2 x 2) = (4) 3.3.2. Using entity modelling, define a new entity composed of the original multivalued attribute. Include your student number in formulating the entity name and specify the PK. (4) 3.3.3. Formulate the new entity’s relation. (2) 3.4. Step 4 of the logical design phase involves mapping binary relations. The DBMS team successfully mapped one-to-many (1:*) and many-to-many (*:*) relationships. The team will now attempt to map the one-to-one (1:1) relationship between the POS_CHECKOUT_STUDENT NUMBER and SHIFT_ALLOCATION_STUDENT NUMBER entities. This 1:1 relationship is informed by the business rule that only one POS device can be checked out for a shift. A shift can be allocated to 1 and a maximum of 4 parking area employees. Each of the four employees assigned to a parking attendant services shift can check out a POS device based on the notion that each person is patrolling a different area of the parking area. So, the relationship between SHIFT_ALLOCATION_STUDENT NUMBER and POS_CHECKOUT_STUDENT NUMBER is a mandatory one. Conversely, in the case of shifts related to car cleaning services, only one employee can check out a POS device on behalf of the group of four10. Therefore, the POS_CHECKOUT_STUDENT NUMBER to SHIFT_ALLOCATION_STUDENT NUMBER relationship is optional. Based on the principles of relational calculus, the service price amount is divided by four. 3.4.1. Create an ERD that comprise multiplicities, association names and association directions to illustrate the 1:1 relationship between POS_CHECKOUT_STUDENT NUMBER and SHIFT_ALLOCATION_STUDENT NUMBER. Examine Coronel et al.’s 10 A vehicle is cleaned by four people. For example, one person is responsible for washing the car with cleaning foam and a high-pressure sprayer, another person will do the drying, someone will do the vacuuming, and so forth. 9 INF3703 – ASSIGNMENT 2 (2020, p. 598) mapping of the “1:1 relationship … between the entities LECTURER and SCHOOL” to guide you. (6) + (2 x 2) = (10) 3.4.2. Formulate the relations of both the POS_CHECKOUT_STUDENT NUMBER and SHIFT_ALLOCATION_STUDENT NUMBER entities. (2) + (4) = (6) 3.5. Step 5 of the logical design phase involves mapping ternary relations (see page 187). Examine the ternary relationship between the entities HOM, POS_DEVICE and SHIFT_ALLOCATION as illustrated in the simplified ERD below. 3.5.1. Formulate the business rules that underpin the ternary relationship. (3 x 2) = (6) End of Question 3 Total: 50