It needs to be done with Oracle SQLAssignment #3 Database Construction and Processing [15%] This...

Question

It needs to be done with Oracle SQLAssignment #3 Database Construction and Processing [15%] This assignment relates to the following Course Learning Requirements: CLR 2 - Develop Advanced Database Design and Normalization CLR 4 - Use Oracle Procedural programming language (PL/SQL) to write programs that contain SQL statements CLR5 - Develop advanced Database Queries] Background You are working for a college, designing a new grading system for students. From talking with the administration, you drafted a conceptual data model. From it you will construct a database and implement basic processing on the data.. Conceptual Data Model Instructions Complete all the steps. Write/paste in the document only the work that’s requested. When submitting your images make sure you show the complete script, requested messages, results, or diagram along with your name and student number. Please format your code so it’s easy to read and crop accordingly. Once you finished your assignment submit this document with your work in the drop box. Always save your scripts to file. You may need to refer back to them at different points in the assignment. Always triple check your semicolons. A semicolon is often the reason your package, function, procedure, or trigger is not compiling. 	Step 	Activity 	1 	Conceptual Data Models Review the conceptual data model. You read it from left-to-right from top to bottom as you would read a western authored book. Notice that parents are mandatory and child optional in most cases in the diagram. This is the de-facto standard. The only time both parents and children are mandatory is according to business rules. For this assignment each DEPARTMENT must have at least one PROGRAM and each PROGRAM must have at least one COURSE. 	2 	Cardinality Charts To find gaps in analysis you have a visual diagram component and a chart component. Visual diagrams help with seeing patterns and complexity. Charts help with seeing similarities at a more lower level. Both are needed for analysis and design. Parents are the 1 in the 1:N relationship. Children are the N in the 1:N relationship. When it’s an M:N or 1:1 relationship, the parent is what the business says it is. For the PROGRAM and COURSE relationship, PROGRAM is the PARENT. An identifying relationship is where the child entity needs attributes from the parent to identify it. The relationship between STUDENT and GRADE is an identifying relationship. A non-identifying relationship is where the child entity doesn’t need any attributes from the parent to identify it. PROGRAM and COURSE have a non-identifying relationship. STUDENT and GRADE have an identifying relationship. Min Cardinality specifies if the entities in the relationship are MANDATORY or OPTIONAL (M or O). Typically parents are MANDATORY while children are OPTIONAL. This should only change if requested by the business because of a business rule. Max Cardinality specifies if the entities in the relationship are 1:1, 1:N, or M:N.   Based on the conceptual data model, fill in the following chart. 	3 	Submit Cardinality Chart 	 	Parent 	Child 	State if Identifying or Non-Identifying 	MIN Cardinality 	MAX Cardinality 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	4 	Create Action Charts Action charts quickly describe the behaviour between a parent and child on how that behaviour enforces the relationships when instances of the entity are manipulated. You always do your best to make parents MANDATORY and children OPTIONAL. That eliminates significant coding you will need to do with triggers when you create your database diagram. To fill out an action chart, you specify the min cardinality of the parent (MANDATORY or OPTIONAL) in the first column. Then you specify the behaviour for each INSERT, UPDATE, and DELETE in the relationship. It’s a good practice to provide the actual names of the parent and the child entities in the chart. It makes it easier to code and to write test scripts. In the parent column (second column) you specify what happens if the parent is inserted/updated/deleted and how that affects the child. In the child column (third column) you specify what happens if the child is inserted/updated/deleted and how that affects the parent. Review the example below for a MANDATORY parent OPTION child relationship. 	5 	Mandatory STUDENT 	STUDENT (parent) 	GRADE (child) 	 	INSERT 	Allow. 	Allow when associated with a STUDENT. 	 	UPDATE 	Prohibit. Using surrogate key. 	Prohibit. Using surrogate key. 	 	DELETE 	Prohibit when a GRADE is associated with a STUDENT, 	Allow. 	 	Fill out the following action chart for SECTION and PROFESSOR based on the conceptual data model. 	6 	Submit Action Chart 	 	 	 	 	 	INSERT 	 	 	 	UPDATE 	 	 	 	DELETE 	 	 	7 	Fill out the following action chart for PROGRAM and COURSE based on the conceptual data model. You will need to specify when to create the parent/child during the INSERT, UPDATE, and DELETE to enforce the entity-to-entity relationship. 	8 	Submit Action Chart 	 	 	 	 	 	INSERT 	 	 	 	UPDATE 	 	 	 	DELETE 	 	 	9 	Creating the Database Design To create your database design diagram, each entity becomes a table. Post-fix each table name with your first/last name initials. The table STUDENT for Bob Charles would be STUDENT_BC. The M:N relationship between PROGRAM and COURSE becomes an intersection table. An intersection table is a table that holds only keys. You name the intersection based on the two tables it’s joining. The first part of the name is the table being joined to on its left The second part of the name is the table its being joined to on its right The end part of the name is _INT to designate the table as an intersection table CLIENT >---- POLICY in the database design Just because a table only contains keys doesn’t mean it’s an intersection table. The table could be a result of normalization. To know the difference, your rely on if the table has or doesn’t’ have the _INT. For each attribute that is plural, you transform it into a table. Confirm you’re following the standard. Each table has a primary ID column StudentID Primary key constraint name arePK StudentPK Foreign key ID column StudentID Foreign key constraint name is FK GradeStudentFK Each column in your database design is atomic Address would become an ADDRESS table with columns AddressID, AddressType, Street, City, Province, and Postal. Create your database design using Oracle Data Modeler based on the conceptual data model. Make each relationship non-Mandatory which will allow NULLs in the foreign key columns. 	10 	Submit Database Design Diagram Add below the screen shot of your Database Design Diagram. The following must be visible: - your name, student number, and current date - all tables in UPPER_CASE - all relationships - all columns in ProperCase - all constraints in ProperCase following the standard Exclude menus, toolbars, and object browsers  	11 	Association Tables are not Intersection Tables A common error is mixing up association tables with intersection tables. An association table tracks the attributes of a relationship. So when it is transformed into database design it is NOT a table containing only keys. They often are named after a business concept or process. For instance, CUSTOMER_REVIEW, could be an association table in your conceptual data. EMPLOYEE_COURSE could be an association table if you want to track which training the employee has or hasn’t taken. Association Table - EMPLOYEE_COURSE Intersection Table - EMPLOYEE_COURSE_INT In the data model & database design Only in the database design Contains attributes Implements M:N relationships. 	12 	Forward Engineer Database Design Generate your SQL script Do not modify your script Run your script as is to create your database 	13 	DEPARTMENT Data Add the following data to your DEPARTMENT table using data entry. If your table name and field names do not match below, change your table so it does. DepartmentID DepartmentName - - - - - - - - - - - - - - - - - - - - - - - - - 1 Accounting 2 Computer Science 3 Engineering 4 Economics 5 Psychology You may have problems with making changes to your tables or inserting data because the constraints won’t allow you. If you do run into this, then drop all table constraints. 	14 	DEPARTMENT Export When databases talk to each other they communicate through extracts. An extract is a subset of a database. Extracts are “packed up” as de-normalized data then sent on the wire. They are then “unpacked” / normalized by another database or system. You can do extracts as an export using SQLPLUS in a .BAT file. A BAT file is a scripting file used in DOS. Each operating system has their own version of the .BAT file. For SQL PLUS to do the export you need to create a CONTROL file. This control file can have the extension .CTL (or .CTRL). You can export multiple tables in a single control file. Create a folder C:\CST2355 Open your text editor Copy and paste the following in the editor… SET MARKUP CSV ON QUOTE OFF SET FEEDBACK OFF SET HEADING OFF SPOOL ; SPOOL OFF Replace  with C:\CST2355\DEPARTMENT.CSV Replacewith SELECT * FROM DEPARTMENT Confirm there are no > / @ Replace  with the user that created the DEPARTMENT table Replace  with your user’s password Replace  with C:\CST2355\EX_DEPARTMENT.CTL Confirm there are no  characters Save the .BAT file to C:\CST2355\EXTRACT.BAT Confirm in C:\CST2355 that the EX_DEPARTMENT.CTL file and the EXTRACT.BAT files both exist Open CMD / DOS and go to the C:\CST2355 folder using DOS commands Open your SQL CLI (Command Line Interface) Type REM, then your student name, student number, and today’s date. For example: REM Bob Smith 123456789 June 4, 2022 Type EXTRACT.BAT then hit enter 	15 	Submit Export Messages Add below the screen shot of your DOS window. The following must be visible: - your name, student number, and current date - full list of departments  	16 	Export Messages Open CMD / DOS and go to the C:\CST2355 folder using DOS commands Open the file DEPARTMENT.CSV file under C:\CST2355 using a text editor In windows you can use the following command at the DOS prompt notepad DEPARTMENT_CSV Put your student name, student number, and today’s date on the first line 	17 	Submit Exported File Add below the screen shot of your text editor window. The following must be visible: - your name, student number, and current date - full list of departments  	18 	DEPARTMENT Import To import an extract into Oracle you can use SQL*LOADER to import the extract into a temporary table. Then run SQL on that temporary table to normalize the data. Just like with exports, you do this using a .BAT file and a .CTL file. Open your text editor Copy and paste the following in the editor… LOAD DATA INFILE  INTO TABLEREPLACE FIELDS TERMINATED BY ',' () Replace  with   'C:\CST2355\DEPARTMENT.CSV'   (confirm you placed single quotes around the filename) Replacewith DEPARTMENT Replacewith DepartmentID, DepartmentName  Confirm there are no  > / control= Replace  with the user that created the DEPARTMENT table Replace  with your user’s password Replace  with 'C:\CST2355\IM_DEPARTMENT.CTL' (confirm you placed single quote around the filename) Confirm there are no >  	20 	PROGRAM, COURSE, PROGRAM_COURSE_INT Data If your table names and field names do not match below, change your tables so they do. Add the following data to your PROGRAM table using data entry ProgramID ProgramName DepartmentID - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - -  1 Business Admin 1 2 Data Science 2 3 Software Design 2 4 Digital Processing 3 5 Robotics 3 Add the following data to your COURSE table using data entry CourseID CourseName - - - - - - - -  - - - - - - - - - - - - 1 Algebra 2 Calculus 3 Programming 4 Assembler 5 Data Analysis Add the following data to your PROGRAM_COURSE table using data entry ProgramCourseID ProgramID CourseID - - - - - - - - -

Assignment #3 Database Construction and Processing [15%]This assignment relates to the following Course Learning Requirements:CLR 2 - Develop Advanced Database Design and NormalizationCLR 4 -...

Get Answer To This Question

Related Questions & Answers

Submit New Assignment