HS7001 Data and Information Management Assignment Sample

HS7001 Data and Information Management Assignment

Task 1

In this task, you first create a new database connection for user ‘Student’ in DBeaver, and then write SQL statements for the following sub-tasks:

- Create a new database named ‘Database11’.

- Create three new users: ‘user1’, ‘user2’, ‘user3’ with the same password ‘helloHolmes’.

- In database ‘Database11’, create a schema named ‘ABC_schema’ and assign ‘user1’ as the owner of this schema.

Task 2

In this task, you first create a new database connection for user ‘user1’ in DBeaver, and then write
SQL statements for the following sub-tasks in schema ‘ABC_schema’:

- Create a table ‘car’.
- Enter data into the table ‘car’.
- Create a view named 'view1' containing the first three columns of table ‘car’.
- Grant the permission to access the schema ‘ABC_schema’ to two user ‘user2’.
- Grant the permission to access the schema ‘ABC_schema’ to two user ‘user3’.
- Grant SELECT permission on table ‘car’ to user ‘user2’.
- Grant SELECT permission on view ‘view1’ to user ‘user3’.

Task 3

In this task, you first create a new database connection for user ‘user2’ in DBeaver, and then write a SQL statement to show all data of table ‘car’ (in schema ‘ABC_schema’).

Task 4

In this taskfor MBA assignment expert, you first create a new database connection for user ‘user3’ in DBeaver, and then write a SQL statement show all data of view ‘view1’ (in schema ‘ABC_schema’).

Solution

Task 1

In this task, you first create a new database connection for user ‘Student’ in DBeaver, and then
write SQL statements for the following sub-tasks:
- Create a new database named ‘Database11’.

SQL

CREATE DATABASE Database11;

- Create three new users: ‘user1’, ‘user2’, ‘user3’ with the same password ‘helloHolmes’.

SQL

CREATE USER user1 WITH PASSWORD 'helloHolmes';

CREATE USER user2 WITH PASSWORD 'helloHolmes';

CREATE USER user3 WITH PASSWORD 'helloHolmes';

- In database ‘Database11’, create a schema named ‘ABC_schema’ and assign ‘user1’ as
the owner of this schema.

We first switch to Database 11 by expanding on the connection string and selecting the Database 11 and opening New SQL Script and then we can now execute the SQL below

SQL

CREATE SCHEMA ABC_schema AUTHORIZATION user1;

Task 2

In this task, you first create a new database connection for user ‘user1’ in DBeaver, and then write
SQL statements for the following sub-tasks in schema ‘ABC_schema’:

We first create a new connection to connect to ABC_schema using user1 which connects successfully as shown below

- Create a table ‘car’.

SQL

CREATE TABLE car (
License_number VARCHAR(20) PRIMARY KEY,
Engine_serial_number VARCHAR(20),
Make VARCHAR(50),
Model VARCHAR(50),
Year INT
);

- Enter data into the table ‘car’.

SQL

INSERT INTO car (License_number, Engine_serial_number, Make, Model, Year) VALUES
('Texas ABC-739', 'A69352', 'Ford', 'Mustang', 2002),
('Florida TVP-347', 'B43696', 'Oldsmobile', 'Cutlass', 2005),
('New York MPO-22', 'X83554', 'Oldsmobile', 'Delta', 2001),
('California 432-TFY', 'C43742', 'Mercedes', '190-D', 1999),
('California RSK-629', 'Y82935', 'Toyota', 'Camry', 2004),
('Texas RSK-629', 'U028365', 'Jaguar', 'XJS', 2004);

- Create a view named 'view1' containing the first three columns of table ‘car’.

SQL

CREATE VIEW view1 AS
SELECT License_number, Engine_serial_number, Make
FROM car;

- Grant the permission to access the schema ‘ABC_schema’ to two user ‘user2’.

SQL

GRANT USAGE ON SCHEMA ABC_schema TO user2;

- Grant the permission to access the schema ‘ABC_schema’ to two user ‘user3’.

SQL

GRANT USAGE ON SCHEMA ABC_schema TO user3;

- Grant SELECT permission on table ‘car’ to user ‘user2’.
GRANT SELECT ON TABLE car TO user2;

- Grant SELECT permission on view ‘view1’ to user ‘user3’.
GRANT SELECT ON view1 TO user3;

Task 3

In this task, you first create a new database connection for user ‘user2’ in DBeaver, and then write
a SQL statement to show all data of table ‘car’ (in schema ‘ABC_schema’).
We first make the connection using user2 as shown below

Connection is successful.
We can now execute the query
SQL
SELECT *
FROM ABC_schema.car;

Task 4

In this task, you first create a new database connection for user ‘user3’ in DBeaver, and then write
a SQL statement show all data of view ‘view1’ (in schema ‘ABC_schema’).
We start by making a connection using user3 which is successful as shown below

Connection is successful so we can now execute the query
SQL
SELECT *
FROM ABC_schema.view1;