Answer:
Step-by-step explanation:
Database Management
If the database management has to be done using a front end online application UI ,
• Club members and dinner caterers can register themselves and update their details
• Club administrators will manage club members and dinner caterers so that they can authenticate the users by verifying the registered users mail address and phone number before they access the website and update their details in the database.
• registered club members and dinners caterers has to be done by club administrators
Techniques used for Performance tuning of the database
• Normalization up to 3NF
• if too much normalization , do denormalization
• clustered indexing and non clustered indexing
• use SQL Optimizer to do statistical analysis by collecting data about the indexes and less optimized path for queries and improve response time
• Use SQL query analyzer will give help analyze the performance of the queries and resolve the performance issues
• Use Sql server Profiler will help monitor which queries and stored procedures degrade the performance by creating a trace , save the trace
• Sql queries should be written in an optimized way avoiding too much of joins or sub queries unless they are needed.
Backup and recovery plan for the database
Using SQL server is an ideal option for backup recovery plan
Backup
• Choose cost effective backup media depending on the requirements such as disc, network share, remote share or cloud
• SQL server Management Studio(SSMS) provides Maintenance Plan Wizard in Sql sever 2019 edition
• Plan on frequency of backup
• use Full backups
• Use transaction log backups
• Use Differential backups
• Automated backup using automated script to cloud services in case of MySQL database using LINUX
Restore
• Restoring a database fully to a transaction which is marked
• Restore backup of a transaction log
• Restoring a database fully to specific point of time
• Based on the edition of the sql server and scenarios we can restore
• full database
• Files
• page
• Database restoring steps can be done using following steps for restoring file
• Missing database file creation
• From the backup device , copy the data to the database file
• Database restoring steps can be done using following steps for restoring database
• databases and transaction logs have to be developed if does not exists already
• From the database's backup media copy data , logs and indexes into database files
How often database backup and types of database backup
• Database back can be scheduled depending upon your servers and requirements of clients . It can be daily , weekly or real time as a continuous automatic backup to cloud services based on the scenarios such as real time or banking or railway or airlines or Hospital Management systems or mission critical systems running as 24X7 services
• Types of backup using SSMS
• Create Full Database Backup
• Back up transaction logs
• Back up Files and Filegroups
• Create a Differential Backups
Difference between the recovery of individual transactions and recovery of entire database
• when individual transactions like insert , update or delete are auto committed or explicitly committed those records can be recovered in small organization accessing databases.
• In case of scenarios where multiple branches of organizations access same huge database which need bulk data insertion or update , Full recovery of data base recovery will be ideal one which has transaction logs to a specific point of time.