Dwayne Fritz
$16/hr
Information Technology
- Reply rate:
- 28.57%
- Availability:
- Full-time (40 hrs/wk)
- Age:
- 23 years old
- Location:
- King Williams Town, Eastern Cape, South Africa
- Experience:
- 3 years
Assignment Cover Sheet
Campus
East London
Module code
ITDSA2-12
Student surname
FRITZ
eVision number
Student name
DWAYNE
Student number pe.2023.z7y6m4
Declaration
“I declare that this assignment is my own original work except for source material explicitly
acknowledged, and that the same or related material has not been previously, or is being
simultaneously, submitted for this or any other course. I also acknowledge that I am aware of
the Institution’s policy and regulations on honesty in academic work as set out in the Eduvos
Conditions of Enrolment, and of the disciplinary guidelines applicable to breaches of such
policy and regulations.”
Student signature
D.F
Date
06/07/2024
Marker Comments
___________________________________________________________________
__________
___________________________________________________________________
_________
v1
Declaration ..................................................................................................................... 1
Marker Comments ......................................................................................................... 1
Deliverbal 1: Database system using Oracle........................................................................ 3
1.1.a. Data Definition Language operations ........................................................................ 3
Deliverbal 2: Database Management (CRUD Operations) MongoDB ................................ 8
v2
Deliverbal 1: Database system using Oracle
1.1.a. Data Definition Language operations
Users TableCREATE TABLE users (
user_id NUMBER PRIMARY KEY,
user_role VARCHAR2(1) CHECK (user_role IN ('A', 'S', 'C')),
user_name VARCHAR2(100),
user_email VARCHAR2(255) UNIQUE,
user_password VARCHAR2(50)
);
Movies TableCREATE TABLE movies (
movie_id NUMBER PRIMARY KEY,
movie_name VARCHAR2(200),
movie_language VARCHAR2(50),
movie_subtitles VARCHAR2(100),
movie_rating VARCHAR2(5)
);
Cinemas TableCREATE TABLE cinemas (
cinema_id NUMBER PRIMARY KEY,
cinema_name VARCHAR2(200),
cinema_address VARCHAR2(255),
cinema_tel VARCHAR2(20),
cinema_email VARCHAR2(100)
);
Rentals TableCREATE TABLE rentals (
rental_id NUMBER PRIMARY KEY,
user_id NUMBER REFERENCES users(user_id),
title_id NUMBER REFERENCES titles(title_id),
book_date DATE,
account_rental_date DATE,
expiry_rental_date DATE
);
Title TableCREATE TABLE titles (
title_id NUMBER PRIMARY KEY,
movie_id NUMBER REFERENCES movies(movie_id),
copy_status VARCHAR2(10) CHECK (copy_status IN ('Available', 'Reserved')),
UNIQUE (movie_id, copy_status)
);
b.Users TableINSERT INTO users (user_id, user_role, user_name, user_email, user_password)
v3
VALUES
(1, 'C', 'Alice Johnson',-, 'securepass123'),
(2, 'C', 'Bob Smith',-, 'strongpassword'),
(3, 'A', 'Admin User',-, 'adminpass'),
(4, 'S', 'Support Agent',-, 'supportpass'),
(5, 'C', 'Charlie Brown',-, 'brownie');
Movie TableINSERT INTO movies (movie_id, movie_name, movie_language, movie_subtitles,
movie_rating)
VALUES
(101, 'Inception', 'English', 'English, Spanish', 'PG-13'),
(102, 'The Matrix', 'English', 'English, French', 'R'),
(103, 'Frozen', 'English', 'English', 'G'),
(104, 'Pulp Fiction', 'English', 'English', 'R'),
(105, 'The Lion King', 'English', 'English', 'G');
Cinemas TableINSERT INTO cinemas (cinema_id, cinema_name, cinema_address, cinema_tel,
cinema_email)
VALUES
(201, 'Cineplex Downtown', '123 Main St', '-',-),
(202, 'Star Cinemas', '456 Elm Ave', '-',-),
(203, 'Sunset Theaters', '789 Oak Rd', '-',-),
(204, 'Silver Screens', '567 Pine Blvd', '-',-),
(205, 'Metroplex Cinemas', '890 Maple Ln', '-',-);
TitlesTableINSERT INTO titles (title_id, movie_id, copy_status)
VALUES
(301, 101, 'Available'),
(302, 101, 'Reserved'),
(303, 102, 'Available'),
(304, 103, 'Reserved'),
(305, 104, 'Available');
Rental TableINSERT INTO rentals (rental_id, user_id, title_id, book_date, account_rental_date,
expiry_rental_date)
VALUES
(401, 1, 301, TO_DATE('-', 'YYYY-MM-DD'), TO_DATE('-', 'YYYY-MMDD'), TO_DATE('-', 'YYYY-MM-DD')),
(402, 2, 302, TO_DATE('-', 'YYYY-MM-DD'), TO_DATE('-', 'YYYY-MMDD'), TO_DATE('-', 'YYYY-MM-DD')),
(403, 3, 303, TO_DATE('-', 'YYYY-MM-DD'), TO_DATE('-', 'YYYY-MMDD'), TO_DATE('-', 'YYYY-MM-DD')),
(404, 4, 304, TO_DATE('-', 'YYYY-MM-DD'), TO_DATE('-', 'YYYY-MMDD'), TO_DATE('-', 'YYYY-MM-DD')),
(405, 5, 305, TO_DATE('-', 'YYYY-MM-DD'), TO_DATE('-', 'YYYY-MMDD'), TO_DATE('-', 'YYYY-MM-DD'));
1.2 Data Selection and Manipulation Operations
a.
v4
UPDATE titles
SET movie_price = 200
WHERE movie_id = 101;
In this statement we can use the WHERE based on the specific movie a person wants to
modify.
b.
c.SELECT
c.user_name AS Customer_Name,
m.movie_name AS Rented_Movie,
r.book_date AS Rental_Date,
(r.account_rental_date - r.book_date) AS Rental_Duration
FROM
rentals r
JOIN
users c ON r.user_id = c.user_id
JOIN
titles t ON r.title_id = t.title_id
JOIN
movies m ON t.movie_id = m.movie_id
WHERE
t.copy_status = 'Reserved';
Views, Index and Function
a.
CREATE VIEW Memberlnfo AS
SELECT *
FROM users;
b.
MEMBERINFO
c.In this particular view, we join the titles and the movies tables to get movies titles. The case
statement calculates the expected return date for rented copies. You can also use the left join
with the rentals table to add rental information.
d.MOVIE_VIEW
v5
e. CREATE INDEX title_description ON titles(description);
f. CREATE OR REPLACE FUNCTION MovieTitleDiscount(movie_id NUMBER)
RETURN NUMBER
IS
original_price NUMBER;
discounted_price NUMBER;
BEGIN
SELECT movie_price INTO original_price
FROM titles
WHERE title_id = movie_id;
discounted_price := original_price * 0.6; -- 40% discount
RETURN discounted_price;
END;
/
g. SELECT
title_id,
movie_name AS Title,
movie_price AS Current_Price,
ROUND(movie_price * 0.6, 2) AS Discounted_Price
FROM
titles t
JOIN
movies m ON t.movie_id = m.movie_id
WHERE
t.copy_status = 'Reserved';
Table
title_id
title
movie_price
v6
101
Inception
15.00
102
The Matrix
12.50
103
Frozen
10.00
104
Pulp Fiction
18.00
105
The Lion King
14.00
Table
title_id
title
movie_price
Discounted Price
101
Inception
15.00
9.00
102
The Matrix
12.50
7.50
103
Frozen
10.00
6.00
104
Pulp Fiction
18.00
10.80
105
The Lion King
14.00
8.40
Deliverbal 2: Database Management (CRUD Operations) MongoDB
2.1
Departments: Refers to various departments at Eduvos.
v7
Instructors: These are instructors at Eduvos. Each instructor is assigned to one department.
Modules: This simply refers to the different modules available at Eduvos. Each module will
have its own code, formed from the first three letters of its name, and will be taught by an
instructor specialising in that module.
Students: A student is any person registered at Eduvos. Each student is uniquely identifiable
through a student number.
2.2
First, let's define the relationshipsDepartmentInstructor RelationshipThere are many instructors operating under a department.
An instructor can work in only one department.
This is a one-to-many relationship: one department to many instructors.
ModuleInstructor RelationshipEach module will be associated with a specialised instructor.
An instructor would be associated with several modules.
This too will be a one-to-many relationship: one instructor to many modules.
StudentModule RelationshipStudents are enrolled in modules.
Each student will be associated with several modules.
This would mean the relationship was many-to-many—many students to many modules.
(Coronel & Morris, 2022, #)
2.3
Query1 use EduEnrolment
Departments-
db.createCollection("Departments")
v8
Instructorsdb.createCollection("Instructors")
Modulesdb.createCollection("Modules")
Studentsdb.createCollection("Students")
StudentModulesdb.createCollection("StudentModules")
Additionally the script will look as suchuse EduEnrolment
db.createCollection("Departments")
db.createCollection("Instructors")
db.createCollection("Modules")
db.createCollection("Students")
db.createCollection("StudentModules")
2.4
Departments Collection
db.departments.insertMany([
{ _id: 1, name: "Computer Science", location: "Building A" },
{ _id: 2, name: "Mathematics", location: "Building B" },
v9
{ _id: 3, name: "Physics", location: "Building C" },
{ _id: 4, name: "Business Administration", location: "Building D" },
{ _id: 5, name: "Engineering", location: "Building E" }
]);
Instructors Collection
db.instructors.insertMany([
{ _id: 101, name: "John Doe", departmentId: 1 },
{ _id: 102, name: "Jane Smith", departmentId: 2 },
{ _id: 103, name: "Michael Johnson", departmentId: 3 },
{ _id: 104, name: "Emily Brown", departmentId: 4 },
{ _id: 105, name: "David Lee", departmentId: 5 }
]);
Modules Collection
db.modules.insertMany([
{ _id: "CS101", name: "Introduction to Programming", instructorId: 101 },
{ _id: "MATH201", name: "Calculus I", instructorId: 102 },
{ _id: "PHY301", name: "Quantum Mechanics", instructorId: 103 },
{ _id: "BA401", name: "Marketing Strategies", instructorId: 104 },
{ _id: "ENG501", name: "Electrical Circuits", instructorId: 105 }
]);
Students Collection
db.students.insertMany([
{ _id: 1001, name: "Alice Johnson", modules: ["CS101", "MATH201"] },
{ _id: 1002, name: "Bob Smith", modules: ["PHY301", "BA401"] },
{ _id: 1003, name: "Eva Brown", modules: ["ENG501"] },
{ _id: 1004, name: "Daniel Lee", modules: ["CS101", "ENG501"] },
{ _id: 1005, name: "Grace White", modules: ["MATH201", "BA401"] }
]);
StudentModules (for the many-to-many relationship)
db.studentModules.insertMany([
{ _id: 2001, studentId: 1001, moduleId: "CS101" },
{ _id: 2002, studentId: 1002, moduleId: "PHY301" },
{ _id: 2003, studentId: 1003, moduleId: "ENG501" },
{ _id: 2004, studentId: 1004, moduleId: "CS101" },
v10
{ _id: 2005, studentId: 1005, moduleId: "MATH201" }
]);
(Coronel & Morris, 2022, #)
2.5
db.Students.find().sort({ name: 1 })
2.6
db.Instructors.find().sort({ _id: 1, name: -1 })
v11
2.7
use EduEnrolment
db.Students.find({ name: { $regex: /^A/ } })
2.8
use EduEnrolment
db.Modules.updateOne(
v12
{ _id: "CS101" },
{ $set: { name: "Advanced Programming" } }
Displaying the updated recorddb.Modules.find()
2.9
use EduEnrolment
db.Instructors.deleteOne({ _id: 50001 })
v13
2.10
use EduEnrolment
db.createCollection("Departments")
db.createCollection("Instructors")
db.createCollection("Modules")
db.createCollection("Students")
db.createCollection("StudentModules")
db.departments.insertMany([
{ _id: 1, name: "Computer Science", location: "Building A" },
{ _id: 2, name: "Mathematics", location: "Building B" },
{ _id: 3, name: "Physics", location: "Building C" },
{ _id: 4, name: "Business Administration", location: "Building D" },
{ _id: 5, name: "Engineering", location: "Building E" }
]);
db.instructors.insertMany([
{ _id: 101, name: "John Doe", departmentId: 1 },
{ _id: 102, name: "Jane Smith", departmentId: 2 },
{ _id: 103, name: "Michael Johnson", departmentId: 3 },
{ _id: 104, name: "Emily Brown", departmentId: 4 },
{ _id: 105, name: "David Lee", departmentId: 5 }
]);
db.modules.insertMany([
{ _id: "CS101", name: "Introduction to Programming", instructorId: 101 },
{ _id: "MATH201", name: "Calculus I", instructorId: 102 },
{ _id: "PHY301", name: "Quantum Mechanics", instructorId: 103 },
{ _id: "BA401", name: "Marketing Strategies", instructorId: 104 },
{ _id: "ENG501", name: "Electrical Circuits", instructorId: 105 }
]);
v14
db.students.insertMany([
{ _id: 1001, name: "Alice Johnson", modules: ["CS101", "MATH201"] },
{ _id: 1002, name: "Bob Smith", modules: ["PHY301", "BA401"] },
{ _id: 1003, name: "Eva Brown", modules: ["ENG501"] },
{ _id: 1004, name: "Daniel Lee", modules: ["CS101", "ENG501"] },
{ _id: 1005, name: "Grace White", modules: ["MATH201", "BA401"] }
]);
db.studentModules.insertMany([
{ _id: 2001, studentId: 1001, moduleId: "CS101" },
{ _id: 2002, studentId: 1002, moduleId: "PHY301" },
{ _id: 2003, studentId: 1003, moduleId: "ENG501" },
{ _id: 2004, studentId: 1004, moduleId: "CS101" },
{ _id: 2005, studentId: 1005, moduleId: "MATH201" }
db.Students.find().sort({ name: 1 })
]);
Bibliography:
Create a new MongoDB program - myCompiler - myCompiler
Oracle Live SQL
Carlos Coronel; Steven Morris; Database Systems: Design, Implementation, &
Management.
v15
v16
