Спрощена база даних для обліку пацієнтів лікарні SQL

Платформа автоматично перекладає опис англійською мовою, для читабельного формату потрібно вимкнути автоматичний переклад біля назви проєкту.

Вимоги до бази даних наведені нижче. Сама структура та опис проєкту надані в закріпленому файлі. 

Проєкт має бути виконаний англійською мовою. Проєкт не включає в себе інтерфес для користувачів, це має бути лише код SQL.

• The application should contain a database layer and an application layer written in PL/SQL, respectively in T-SQL. The goal is not to write a user interface.
• The application will contain a suitable number of interrelated tables. A suitable number is 7-10 tables plus/minus according to the application type. 
• The solution should include:
  • Definition of necessary tables, including definitions of primary, candidate and foreign keys.
  • Definition of suitable indexes including those through all foreign keys.
  • Definition of other integrity constraints to ensure the integrity of the data input.
  • Triggers monitoring things that can not be monitored at constraint level.
  • The non-existent user interface should be simulated by the views corresponding to the individual screens. E.g.:
    • List of customers including data from (... to one) associated tables
    • List of borrowings, including customer data and borrowed titles.
    • List of borrowings after the deadline.
    • List of available books for book titles
    • etc. (depending on the application)
  • Individual tables (at least the main ones) should have defined the packages of procedures, functions and other necessary objects that manipulate the entities as it is usual in object programming and represent the application layer. Procedures and functions should make sure that input data is meaningful. Resp. they should be aware of the fact that triggers and constraints can refuse some manipulations and maintain exceptions correctly. Example of methods for customers in the library:
    • Adding a new customer.
    • Customer deletion (if nothing is lent)
    • New borrowing registration.
    • Return of a specific loan.
    • Return of all customer loans.
    • Calculation of penalties for exceeding the deadline
    • etc. (depending on the application)
• The schema should be reasonably indexed so that queries for all view data do not require more than one FULL-SCAN (on the control table). SQL statements in procedures and functions should not require any FULL-SCAN except for specific situations because they handle a limited set of lines.
• Take into account the parallel execution of multiple instances of the program over shared data. Critical sections of programs should appropriately use transaction capabilities, including table locking and row locking to achieve data consistency.
• The solution should include at least the following scripts:
  1. Creation of the whole schema.
  2. Insertion of demo-data
  3. Generating statistics for all schema tables (in Oracle)
  4. Dropping statistics for all schema tables (in Oracle)
  5. Correct dropping of the whole schema content
  6. A test script that would demonstrate the functionality of all proposed public methods in the procedural interface and view functionality. For example:
     

     --how look all customers before the changes?
      select * from view_on_customers;
      --add another customer to the list of customers ...
      exec db_customer.new_customer(...);
      --... we will change the address ...
      exec db_customer.customer_change(...);
      --... we try to delete the customer with loans ...
      exec db_customer.delete_customer(...);
      -- ... and someone without them
      exec db_customer.delete_customer(...);
      --How do the customers look after changes?
      select * from view_on_customers;
      ...

• Don't forget to comment your scripts
  • For each script, it should be possible to recognize the author.
  • In script 1), the assignment should be described.
  • Script 1) should describe the meaning of individual objects in the schema, including table columns and procedural and function parameters
  • Script 6) should describe what is being tested by the call, and especially that the call should end up with an error (what, why).
  • For more complex SQL queries and commands, write what they are doing. Without comment, the meaning of the command is hard to find.