Technical Report

Instructions: Describe any risks associated with the database design and proposed mitigation strategies.

Design Decision:

To ensure a database effectively works, it is essential to consider that the system design can grow with your data. In this case, the creation of the database should handle the increasing number of students and the courses that are undertaken. Some of the decisions made that will impact the nature of this database include the usability of the system. In this, project the platform chosen is user-friendly for all the members of staff required to use it, as it is also easier to set different levels of permission for other teams or personnel (Sug, 2020, June).

Oracle is set to handle the operations in terms of the DBMS (Database Management System) chosen to run the database. Some of the positive impacts related to the selected database design include; portability and version changes. Oracle enables portability, which means it can be ported to all different platforms and efficiently runs on close to 20 different networking protocols. In addition to that, it also can run on more than 100 hardware platforms, and any changes that are required to be done in the database is easy and safe both in the software and hardware parts. Regarding the version changes, it is easily upgradable to the newer version. Also, it offers good rollback compatibility in case of any upgrade issues without rewriting any data.

It is also imperative to analyze the cons of the DBMS when choosing a design decision, including the cost and complexity of the database. In this case, the cost of Oracle is very high compared to its competitors. Also, the price of licensing is very inconsistent and changes over time due to policy changes. Concerning complexity, this is one of the significant disadvantages of Oracle DBMS. It requires users to have the proper technical ability to install, operate, and maintain an incredibly complex engine.

Key Factors Influencing Design:

In this section, our focus will be on the key-functional and non-functional requirements of the system. The functional requirements dictate what the system does or what it must not do, including; the product features and the user requirements. If the functional requirements are not met, the system will not work (TANIŞ, 2018). In this project, the database is designed to store performance information of students in each subject of medical school. So some of the product features of the design will include; Add students, to enable adding students to the system, Add Course, to allow adding courses undertaken, Add results, to allow adding of results after an exam, Update, to enable update and editing feature and Delete, to enable deletion of an entry.

On the other hand, the non-functional requirements dictate how the system should execute the functions. These do not affect the system's basic functionality; hence, if not met, the system will still run, but they are essential because they improve usability. Some of the no-functional requirements include; product properties and user expectations. Some of the non-functional requirements include; the system will be browser-based, which implies that the user can access the system from anywhere as long as they have an internet connection. It is set to run on a Mac operating system platform which is quite an efficient platform. It can support customized reports; the system is set to generate different types of reports.

A few critical requirements need to be addressed concerning the functionality: availability, performance and scalability, security and backup and recovery. In this project, Oracle DBMS is designed to support these critical functionalities. For instance, in terms of availability, Oracle can provide all-time data availability, including planned or unplanned failures and downtimes. Features like portability and Real Application Clustering enable efficient performance and scalability of Oracle. Oracle addresses the topmost important feature: security; it is easy to prevent unauthorized access and allow specific access to users. Oracle layout is also designed in a way that it is entirely recoverable from all kinds of failures.

Functional Design Decision:

The five major components of database design that will dictate the functionality and efficiency of the database are; hardware, software, data, procedures and users (Sug, 2020, June). In these projects, Mac operating system is chosen to run the hardware and software part, and there this is a very efficient platform as it provides the best and secure access to data. Also, the data to be stored is for medical students and generally a school database considering the sensitivity and amount of information; the platforms chosen can handle these comfortably.

Regarding procedures, these refer to instructions used in a DBMS, that is, the setup of the database and management of the daily routine; in this project, Oracle is set to handle the database operations and can manage the day-to-day on-site operations. It is also essential to consider the users of the system and the technical capability that they have to make a considerate functional design decision. In this case, the users are students and teachers who will be accessing the information, and hence the platforms chosen are user-friendly to them.

In these projects, a relational DBMS (Oracle) is used, and one advantage is that it can differentiate between logical operations and physical operations. The logical operations specify the type of content required. For instance, the school application can request a student name or add a student record to a table. On the other hand, physical operations specify how things should be carried out and performed the procedure. In this project, Oracle RDBMS will store and retrieve data to enable the physical functions to be transparent to the database applications.

Concerning inputs and the outputs that will be given. Student and course details will be the inputs and reports concerning exam results, and course progress will be the output that will be XHTML compatible, in that the interface will be the same for all internet browsers. The user can access the system using any browser.

Performance and Maintenance Design Decision:

When considering decisions concerning performance and maintenance, the school has information that it has to store and manage to meet its requirements and needs. The school should collect and maintain student data, and this information should strictly only be available for the persons who need it.

The nature of the database design in this project is the distributed database design model; hence, the users will also be able to access and manipulate data. In case of fail in one site, a backup can be used; updates on one entity will have to synchronize to update the other entities of the same record.

In these projects, concerning performance and maintenance, Oracle RDBMS chosen to carry out the tasks provides the following; Schema object types, which will enable creation and manipulation of other types of schema objects which include; tables, to store data in rows, indexes, that will allow direct and fast access to rows, views, to enable customized presentations of data in one or more tables, sequences, which will handle the creation of primary key values and partitions which will handle decomposing of extensive tables and indexes into smaller and manageable pieces called partitions.

Data integrity is an essential part of maintaining data, hence in this project, when designing the database application to be used, some of the techniques to enable guaranteed security include; enhancing rules with triggered stored database procedures, enforcing rules inside the code of the database application and use of the Oracle database integrity constraints, which are rules that enable restriction of values in the database. In terms of data access, Oracle data processes DDL differently from DML and hence the database can fetch set rows or in groups and sort the rows as per query.

Oracle transaction management feature enables the use of transactions to ensure that there is data concurrency and consistency; this means that the school database, which will be multiuser, is data concurrent; hence multiple users can be access data at the same time, and there is data consistency, in that each user can see a consistent view of the data inclusive of any changes made on their data. This will enable the teachers to update results on the student data, and all the students will be able to view the results of the Course they are undertaking and any updates of the results by the teachers.

Another essential feature taken into consideration in this project is the database storage structures. Oracle enables both physical and logical storage structures and separates the two; hence you can manage the physical storage without affecting the logical systems. For instance, when you rename the database, the table in the database will remain unchanged. The physical storage structures will handle the data and temp files, control files and online redo logo files. The logical storage units handle the data blocks, extents, segments and tablespaces.

Statement of Work:

Overview:

This project will oversee the design and creation of the database to track and store information about passing/failing students in four years of medical school in each subject. It will serve as the data store that enables schools to browse the information to see the process of students in each subject: Anatomy/Embryo, Histology, Pathology, Pharmacology, Microbiology, Genetics, Biochemistry, Physiology, and Social Science. In addition, this database helps the schools see if any challenges at each subject cause the students not to reach the passing score and create a new pathway to increasing the number of students passing the subjects.

Executive Summary

The database will create full picture of how students perform in each subject and how the knowledge is delivered to the students. The database will also help the professors/school see where the student stands in the subject that there may be something that they can help or change to increase the number of students successful in the subject. Students will be able to use unlimited many different sources to study, unlimited sources for Question banks. The database should be designed to allow for the easy addition of new search criteria and maintain the school to improve the number of students passing the subject. From the database, professors/schools will be a better picture of the challenges that students might face that affect the performances.

Project Scope

This database system is designed to store performance information of students in each subject of medical school. This creation can show the complete picture of if anything might affect the performances which school can help improve or fix the problems. In scope, work will include documenting the project requirements, modeling the database in entity-relationship form. The creation of DML scripts to simulate the interaction between databases, challenges in each subject, and improve pathways.

· The number of students who do not reach the passing score in each subject

· The challenges that those students have during their studying

· The sources they use to study(books, questions bank, lecture PowerPoint)

· The pathway to help students improve their performances

Database Benefits, Goals, Expectation

Upon completing the project, the database shall contain all the essential information( challenges) students face in each subject. In addition, this project might give ideas for students to change the way of approaching the subjects, and professors/schools to have a better way to deliver the information to the students. Deliverables include this statement of work document, an entity-relationship diagram defining the structure of the database, DDL scripts for creating the database, DML and SQL scripts that demonstrate proper usage of the database, and a final report on the project as a whole.

Project Hardware and Software Tools

· ER-Assistant, running on Mac

· Microsoft Office, running on Mac

· Oracle Database by using Virtual Desktop Access(VDA)

· Google Chrome

· DDL and DML

After creating the ERD, I will use the Data Definition Language to create the database structure and define the information for this project. The initial database tables will include the number of pass/fail students in each subject, the challenge students face in each subject, the sources they use during studying, the recommendations from professors. I will also use the Data Manipulation Language (DML) to insert rows of information regarding the students' information and improve their performances.

Requirement Analysis:

The project is mainly about the challenges that students has to face for each subject that cause students did not reach the goal. This paper will explain the relationship in details of the ERD that will attach to this documents.

· Student Entity has the Student_ID as primary key without foreign key. It also has 3 relationship with the Sources Entity, Grade Entity and Courses Entity. Grade Entity has the primary key is Student_ID and foreign key is Course_ID. Sources Entity only has primary key is Book_ID.

· Student can receives one, two or many grades and the relationship is mandatory. Grades has optional 1:1 relationship with the Student.

· Student can use one, two or many sources and the relationship is optional(1:M)

· Student also has the mandatory relationship with Course Entity as 1:M, student can have zero, one or many courses. The course can be taken by zero, one or many students (1:M).

· Challenges Entity is the Supertype( Parent Entity) and also has 7 child entities that describe the challenges that student might have by each subjects. The primary key and foreign key are Course_ID.

· Courses Entity is also a Supertype( Parent Entity)has primary key as Course_ID and foreign key as Grade_Type. The other 7 child entities from the Courses Entity which is the 7 different main subjects that medical students has to study in the first 2 years of Basic Science. The foreign key are the same in all subject are Course_ID and Student_ID.

· Anatomy/Embryo has primary key as Body_Area

· Chemistry/genetic has primary key as Target_gene

· Pathology has primary key as Diagnosis

· Pharmacology has primary key as Mechanism of action

· Physiology has primary key as Hormones_Pathway

· Microbiology has primary key as Species_Types

· Social Science has primary key as Doctor/Patient Relationship

· Each of the Courses’ child entity will associate with Challenges’ child entity. Each subject challenge has zero, one or many challenge as mandatory. Each of the subject might have zero, one or many challenges as optional relationship.

· Each of the Challenges_Subject entities show the challenges that students have in each subject without primary key in the attributes. Because the challenge of one subject might repeat in other challenges subject so there will be no unique to have primary key in this case( like Time_management or high volume of content). All the child entities have the same foreign key is Time_Management.

· The Challeneges Entity has primary as Time_Managment and foreign key as Course_ID and Student_ID

· Business rules:

· Student cannot enroll for more than 2 courses per semester

· Student cannot fail more than 1 course per semester.

· Student can use zero, one or more sources during the semester to help for understanding the topic.

Assumption and Consideration

There are more challenges that students might face but only use the most common challenges in this project. In addition, there is a problem about the price different in the sources which I did not include in this ERD model because I am not sure if that is important or it consider as challenge in this project.

The primary key and foreign key are king of repeat in some of the entities which might cause confusing for people who read the ER diagram.

Detailed Database Design:

DDL Source Code:

drop table if exists challenge_Anato;

drop table if exists challenge_Bio;

drop table if exists challenge_Pharmacology;

drop table if exists challenge_Pathology ;

drop table if exists challenge_Physiology ;

drop table if exists challenge_Microbiology ;

drop table if exists challenge_SS ;

drop table if exists challenges ;

drop table if exists Anatomy ;

drop table if exists chemistry ;

drop table if exists Pathology ;

drop table if exists Pharmacology ;

drop table if exists Physiology ;

drop table if exists Microbiology ;

drop table if exists social_science ;

drop table if exists courses ;

drop table if exists student ;

drop table if exists grade ;

drop table if exists sources ;

drop table if exists challenge_Anato;

create table if not exists challenge_Anato (

Expensive_source varchar(255) null,

Lab_resources varchar(255) null,

Visiblle_body_reg varchar(255) null,

Large_Volume varchar(255) null,

High_volume varchar(255) null ,

time_manag timestamp null

);

desc challenge_Anato ;

drop table if exists challenge_Bio;

create table if not exists challenge_Bio (

Remember_all_meta varchar(255) null,

Difficult_understand varchar(255) null,

Too_many_names varchar(255) null,

The_king_of_borir varchar(255) null,

time_manag timestamp null

);

desc challenge_Bio ;

drop table if exists challenge_Pharmacology;

create table if not exists challenge_Pharmacology (

too_much_path varchar(255) null,

similar_names varchar(255) null,

side_effects varchar(255) null,

dosing varchar(255) null,

contraindicated varchar(255) null,

time_manag timestamp null

);

desc challenge_Pharmacology ;

drop table if exists challenge_Pathology ;

create table if not exists challenge_Pathology (

Understand_micro varchar(255) null,

High_volume_lab varchar(255) null,

Mixed_disease varchar(255) null,

same_symptoms varchar(255) null,

Best_treatments varchar(255) null,

time_manag timestamp null

);

desc challenge_Pathology ;

drop table if exists challenge_Physiology ;

create table if not exists challenge_Physiology (

Blockages varchar(255) null,

Hormones_type varchar(255) null,

Hormones_effect varchar(255) null,

hormones_local varchar(255) null,

drug_effects varchar(255) null,

time_manag timestamp null

);

desc challenge_Physiology ;

drop table if exists challenge_Microbiology ;

create table if not exists challenge_Microbiology (

species_types varchar(255) null,

species_symptom varchar(255) null,

species_treatment varchar(255) null,

species_pathway varchar(255) null,

resistance_type varchar(255) null,

time_manag timestamp null

);

desc challenge_Microbiology ;

drop table if exists challenge_SS ;

create table if not exists challenge_SS (

similar_disease varchar(255) null,

drugs_adult varchar(255) null,

high_volume varchar(255) null,

timelength varchar(255) null,

drugs_available varchar(255) null,

create_new_sym varchar(255) null,

time_manag timestamp null

);

desc challenge_SS ;

drop table if exists challenges ;

create table if not exists challenges (

course_ID varchar(255) null,

student_id varchar(255) null,

sources varchar(255) null,

grade_effects varchar(255) null,

time_manag timestamp not null,

primary key (time_manag)

);

desc challenges ;

drop table if exists Anatomy ;

create table if not exists Anatomy (

body_area varchar(255) not null,

student_id varchar(255) null,

course_id varchar(255) null,

nerves_veins varchar(255) null,

lab varchar(255) null,

primary key (body_area)

);

desc Anatomy ;

drop table if exists chemistry ;

create table if not exists chemistry (

target_gene varchar(255) not null,

student_id varchar(255) null,

course_id varchar(255) null,

pathways varchar(255) null,

molecular_data varchar(255) null,

proteins varchar(255) null,

techniques varchar(255) null,

primary key (target_gene)

);

desc chemistry ;

drop table if exists Pathology ;

create table if not exists Pathology (

Diagnosis varchar(255) not null,

student_id varchar(255) null,

course_id varchar(255) null,

Patient_COnditior varchar(255) null,

Lab_values varchar(255) null,

Tests_perform varchar(255) null,

Microscopy varchar(255) null,

primary key (Diagnosis)

);

desc Pathology ;

drop table if exists Pharmacology ;

create table if not exists Pharmacology (

Mechanism varchar(255) not null,

student_id varchar(255) null,

course_id varchar(255) null,

Side_effects varchar(255) null,

drugs_types varchar(255) null,

drug_relationship varchar(255) null,

primary key (Mechanism)

);

desc Pharmacology ;

drop table if exists Physiology ;

create table if not exists Physiology (

Hormones_P varchar(255) not null,

student_id varchar(255) null,

course_id varchar(255) null,

Side_effects varchar(255) null,

Diseases varchar(255) null,

Treatments varchar(255) null,

Hormones varchar(255) null,

primary key (Hormones_P)

);

desc Physiology ;

drop table if exists Microbiology ;

create table if not exists Microbiology (

species_type varchar(255) not null,

student_id varchar(255) null,

course_id varchar(255) null,

Diseases varchar(255) null,

Treatments varchar(255) null,

primary key (species_type)

);

desc Microbiology;

Drop table if exists social_science ;

create table if not exists social_science (

doctor varchar(255) not null,

student_id varchar(255) null,

course_id varchar(255) null,

psychology varchar(255) null,

Treatments varchar(255) null,

pediatric_psychology varchar(255) null,

patient_privacy varchar(255) null,

primary key (doctor)

);

desc social_science;

drop table if exists student ;

create table if not exists student (

student_levels varchar(255) null,

student_id varchar(255) not null,

student_address varchar(255) null,

courses_enroll varchar(255) null,

retake_classes varchar(255) null,

primary key (student_id)

);

desc student ;

drop table if exists grade ;

create table if not exists grade (

exam_name varchar(255) null,

student_id varchar(255) not null,

gpa varchar(255) null,

pass_fail varchar(255) null,

course_id varchar(255) null,

primary key (student_id)

);

desc grade ;

drop table if exists sources ;

create table if not exists sources (

kaplan varchar(255) null,

book_id varchar(255) not null,

uworld varchar(255) null,

lippincott varchar(255) null,

question_banks varchar(255) null,

practice_question varchar(255) null,

primary key (book_id)

);

desc sources ;

drop table if exists courses ;

create table if not exists courses (

timelength varchar(255) null,

course_id varchar(255) not null,

instructor_id varchar(255) null,

grade_type varchar(255) null,

course_description varchar(255) null,

primary key (course_id)

);

desc courses ;

alter table challenges add foreign key (course_ID) references courses(course_id) ;

alter table challenges add foreign key (student_id) references student (student_id ) ;

alter table challenge_Anato add foreign key (time_manag) references challenges(time_manag) ;

alter table challenge_Bio add foreign key (time_manag) references challenges(time_manag) ;

alter table challenge_Pharmacology add foreign key (time_manag) references challenges(time_manag) ;

alter table challenge_Pathology add foreign key (time_manag) references challenges(time_manag) ;

alter table challenge_Physiology add foreign key (time_manag) references challenges(time_manag) ;

alter table challenge_Microbiology add foreign key (time_manag) references challenges(time_manag) ;

alter table challenge_SS add foreign key (time_manag) references challenges(time_manag) ;

alter table Anatomy add foreign key (course_id) references courses(course_id) ; ;

alter table Anatomy add foreign key (student_id) references student (student_id ) ;

alter table chemistry add foreign key (course_id) references courses(course_id) ; ;

alter table chemistry add foreign key (student_id) references student (student_id ) ;

alter table Pathology add foreign key (course_id) references courses(course_id) ; ;

alter table Pathology add foreign key (student_id) references student (student_id ) ;

alter table Pharmacology add foreign key (course_id) references courses(course_id) ; ;

alter table Pharmacology add foreign key (student_id) references student (student_id ) ;

alter table Physiology add foreign key (course_id) references courses(course_id) ; ;

alter table Physiology add foreign key (student_id) references student (student_id ) ;

alter table Microbiology add foreign key (course_id) references courses(course_id) ; ;

alter table Microbiology add foreign key (student_id) references student (student_id ) ;

alter table social_science add foreign key (course_id) references courses(course_id) ; ;

alter table social_science add foreign key (student_id) references student (student_id ) ;

alter table grade add foreign key (course_id) references courses(course_id) ;

create index course_ID on challenges(course_ID) ;

create index challenges1 on challenges(grade_effects) ;

create index challenges2 on challenges(student_id) ;

create index challenge_Anato on challenge_Anato(time_manag);

create index challenge_Anato1 on challenge_Anato(Lab_resources);

create index challenge_Bio on challenge_Bio (time_manag);

create index challenge_Bio1 on challenge_Bio (Too_many_names);

create index challenge_Pharmacology on challenge_Pharmacology (time_manag);

create index challenge_Pharmacology1 on challenge_Pharmacology (dosing);

create index challenge_Pathology on challenge_Pathology (time_manag);

create index challenge_Pathology1 on challenge_Pathology (Best_treatments);

create index challenge_Physiology on challenge_Physiology (time_manag);

create index challenge_Physiology2 on challenge_Physiology (Hormones_type);

create index challenge_Microbiology on challenge_Microbiology (time_manag);

create index challenge_Microbiology2 on challenge_Microbiology (species_types);

create index challenge_SS on challenge_SS(time_manag);

create index challenge_SS2 on challenge_SS(similar_disease);

create index Anatomy on Anatomy(course_id) ;

create index Anatomy2 on Anatomy(student_id) ;

create index Anatomy3 on Anatomy(lab) ;

create index chemistry on chemistry(course_id) ;

create index chemistry2 on chemistry (student_id);

create index chemistry3 on chemistry(proteins) ;

create index Pathology on Pathology(course_id);

create index Pathology2 on Pathology (student_id);

create index Pathology3 on Pathology(Tests_perform);

create index Pharmacology on Pharmacology(course_id) ;

create index Pharmacology2 on Pharmacology(student_id);

create index Pharmacology3 on Pharmacology(Side_effects) ;

create index Physiology on Physiology (course_id);

create index Physiology2 on Physiology (student_id);

create index Physiology3 on Physiology (Diseases);

create index Microbiology on Microbiology(course_id) ;

create index Microbiology2 on Microbiology(student_id) ;

create index Microbiology3 on Microbiology(Treatments) ;

create index social_science on social_science(course_id) ;

create index social_science2 on social_science (student_id);

create index social_science3 on social_science(psychology) ;

/* view will show grade table and student table */

-- Create Views --

create view grade_views as

select * from grade;

desc grade_views;

create view student_views as

select * from student;

show full tables;

-- Create Sequences --

alter table sources modify column book_id int not null auto_increment;

alter table sources add constraint u_sources unique(book_id);

desc sources;

/*if gpa is below than 2 then it will set pass_fail to fail and

if gpa is greater than 4 then it will set pass_fail to pass and -- */

-- Create Triggers --

delimiter $$

create trigger befor_insert_record

before insert on grade for each row

begin

update grade set pass_fail="fail" where gpa<2;

end;

$$

delimiter $$

create trigger before_insert_passfail_data

before insert on grade for each row

begin

update grade set pass_fail="pass" where gpa>4;

end;

$$

show triggers;

USE challenge;

WITH vars

AS (

SELECT

DATABASE() AS v_SchemaName -- Do not change this value...it is changed up above on line 11 and passed thru to here

, 'NO' AS v_TablesOnly -- YES=Limit To Tables only; NO=Include views too

)

, baseTbl

AS (

SELECT table_schema AS SchemaName

, table_catalog

, table_type, table_name, table_schema

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = (SELECT v_SchemaName FROM vars)

AND ( (TABLE_TYPE = 'BASE TABLE')

OR ((SELECT v_TablesOnly FROM vars) = 'NO')

)

)

, metadata

AS (

SELECT

bt.SchemaName AS schema_nm

, bt.table_name AS table_nm

, CASE WHEN bt.TABLE_TYPE = 'BASE TABLE' THEN 'TBL'

WHEN bt.TABLE_TYPE = 'VIEW' THEN 'VW'

ELSE 'UK'

END AS obj_typ

, tut.ordinal_position AS ord_pos

, tut.column_name AS column_nm

, CONCAT(COALESCE(tut.data_type, 'unknown'),

CASE WHEN tut.data_type IN('varchar','char') THEN CONCAT('(', tut.CHARACTER_MAXIMUM_LENGTH, ')')

WHEN tut.data_type IN('date','time') THEN CONCAT('(3)')

WHEN tut.data_type = 'datetime' THEN CONCAT('(8)')

WHEN tut.data_type = 'timestamp' THEN CONCAT('(4)')

WHEN tut.data_type in('tinyint','smallint','mediumint','int','bigint') THEN CONCAT('(', tut.NUMERIC_PRECISION, ')')

WHEN tut.data_type = 'decimal' THEN CONCAT('(', tut.NUMERIC_PRECISION, ',', tut.NUMERIC_SCALE, ')')

WHEN tut.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CONCAT('(', tut.CHARACTER_MAXIMUM_LENGTH, ')')

WHEN tut.DATETIME_PRECISION IS NOT NULL THEN CONCAT('(', tut.DATETIME_PRECISION, ')')

WHEN tut.NUMERIC_PRECISION IS NOT NULL

AND tut.NUMERIC_SCALE IS NULL THEN CONCAT('(', tut.NUMERIC_PRECISION, ')')

WHEN tut.NUMERIC_PRECISION IS NOT NULL

AND tut.NUMERIC_SCALE IS NOT NULL THEN CONCAT('(', tut.NUMERIC_PRECISION, ',', tut.NUMERIC_SCALE, ')')

ELSE ''

END ) AS data_typ

, CASE WHEN tut.IS_NULLABLE = 'YES' THEN 'NULL' ELSE 'NOT NULL' END AS nullable

FROM INFORMATION_SCHEMA.COLUMNS tut

INNER JOIN baseTbl bt ON bt.table_catalog = tut.TABLE_CATALOG AND bt.table_name = tut.table_name

)

, meta_for_keys

AS (

SELECT schema_nm, table_nm, column_nm

, GROUP_CONCAT(is_key ORDER BY is_key SEPARATOR ',') AS is_key

FROM (

SELECT cons.TABLE_SCHEMA AS schema_nm

, cons.TABLE_NAME AS table_nm

, kcu.COLUMN_NAME AS column_nm

, CASE WHEN cons.constraint_type = 'PRIMARY KEY' THEN 'PK'

WHEN cons.constraint_type = 'UNIQUE' THEN 'UK'

WHEN cons.constraint_type = 'FOREIGN KEY' THEN 'FK'

ELSE 'X'

END AS is_key

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS cons

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu

ON cons.TABLE_SCHEMA = kcu.TABLE_SCHEMA

AND cons.TABLE_NAME = kcu.TABLE_NAME

AND cons.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME

WHERE cons.table_schema = (SELECT v_SchemaName FROM vars)

AND cons.table_name IN(SELECT DISTINCT table_name FROM baseTbl)

AND cons.constraint_type IN('PRIMARY KEY','FOREIGN KEY','UNIQUE')

GROUP BY cons.TABLE_SCHEMA, cons.TABLE_NAME, kcu.COLUMN_NAME, cons.constraint_type

) t

GROUP BY schema_nm, table_nm, column_nm

)

, col_comm

AS (

SELECT TABLE_SCHEMA AS SCHEMA_NM

, TABLE_NAME AS TABLE_NM

, COLUMN_NAME AS COLUMN_NM

, COLUMN_COMMENT AS column_descr

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_schema = (SELECT v_SchemaName FROM vars)

AND table_name IN(SELECT DISTINCT table_name FROM baseTbl)

)

SELECT md.SCHEMA_NM, md.TABLE_NM, md.OBJ_TYP

, md.ORD_POS AS ord

, COALESCE(pk.is_key, ' ') AS is_key

, md.COLUMN_NM, md.DATA_TYP, md.NULLABLE, c.column_descr

FROM metadata md

LEFT JOIN meta_for_keys pk ON pk.SCHEMA_NM = md.SCHEMA_NM AND pk.TABLE_NM = md.TABLE_NM AND pk.COLUMN_NM = md.COLUMN_NM

LEFT JOIN col_comm c ON c.SCHEMA_NM = md.SCHEMA_NM AND c.TABLE_NM = md.TABLE_NM AND c.COLUMN_NM = md.COLUMN_NM

ORDER BY md.SCHEMA_NM, md.TABLE_NM, md.ORD_POS

System Information:

Instructions: Document the DBMS configuration, hardware configuration, database software utilities, and any support software used. If any of these software elements or hardware configurations are not CMS-standard architecture, indicate the date these items were approved or a waiver was granted

Performance Monitoring and Database Efficiency:

Instructions: Provide appropriate detailed subparagraphs that relate to the section named Performance and Maintenance Design Decisions. Describe what parties will be responsible for monitoring performance (to include space utilization, system resource consumption, and query performance metrics), along with tools that will help provide this monitoring. If interfaces with other systems impact maintenance, provide a description of those interfaces with other application software including those of other operational capabilities and from other organizations. For each interface, specify the information described in the following sub-sections

Backup and Recovery:

Instructions: Describe required strategies and scheduling for periodic backups of the data. If certain objects have differing requirements, provide a breakdown by object. Describe the methodology for reestablishment or recreation of the necessary data schema and system support files.

References

Kurnianda, N. R. (2018). Database Design for Customer Retention and Loyalty Administration Information System. IJCSMC:: International Journal of Computer Science and Mobile Computing, 7(10), 1-8.

Sug, H. (2020, June). Efficient checking of functional dependencies for relations. In Journal of Physics: Conference Series (Vol. 1564, No. 1, p. 012011). IOP Publishing.

Taiwo, O. O., & Afolabi, M. O. (2019). Efficient Database Design and Implementation of Students Academic Results Processing System (SARPS). i-Manager's Journal on Computer Science, 7(4), 1.

TANIŞ, A. (2018). Environmental Sound Database Design and Implementation (Doctoral dissertation, Ankara Yıldırım Beyazıt Üniversitesi Fen Bilimleri Enstitüsü).