Database discussions

We have talked about Keys throughout the semester.

When choosing a Primary Key for your tables, it's critical to choose the correct type.

1.What are the benefits & risks of implementing Surrogate Primary Keys?

2.Why is it necessary to sometimes create a unique index as a constraint on tables with a Surrogate Primary Key?

3.When is it a good or bad idea to use a Natural Key as a Primary Key?

4.In what situations should you use a Composite Primary Key?

5.In some cases, a Surrogate Primary Key or a Composite Primary Key will work.  What factors help you decide to choose one over the other?

6.What Primary Key considerations are there while implementing:Associative Entity tables. 

7.What if the Associative Entity table is the parent of another (child) table?Multivalued Attribute tables.

8.Any other questions or discussion about Primary Keys is welcome.

During week 10, we talked about a very important concept in Database & Software development called, Abstraction.

9.What is Abstraction conceptually?

10.In database development, what objects are used to implement Abstraction and how do they provide Abstraction?

11.What are some real-world examples of Abstraction.

12Why does implementing Abstraction help prevent bugs and ensure scalability of your systems?

In Week 11, we discuss SQL Injection.  Although this is not a super prevalent topic throughout the course, it's important to have a basic understanding of what it is and how to guard against this common vulnerability.  It also comes up in database-related job interviews.

13.What is SQL Injection, and how is this vulnerability exploited?

14.What best practices should you implement as a SQL developer to guard against SQL Injection?

15.Search Google for news stories about SQL Injection attacks and summarize them here.

It's OK to include a URL for reference, but please summarize the story you read about so we understand the story without having to navigate to the URL.

16.Provide some use cases that you come up with on your own.

17.Ask any clarifying questions that you may have.

We talked about Database Normalization just before the Midterm Exam.  Database Normalization is critical to understand during the data modeling phase to ensure you minimize the risk of data anomalies.  Normalization also important to understand while querying the database, as understanding Normalization helps you to understand relational database architecture.

Database Normalization is also very common interview material, and it's important to have a conversational knowledge of it while searching for database jobs.

Please Discuss:

18.What are the types of Data Anomalies that Normalization helps to mitigate?

19.What are the Normal Forms, and how do they prevent Data Anomalies?

20.What are some of the costs & benefits of Normalization? Yes, there are some risks from Normalization, but they typically outweighed by the benefits.

21.Does Normalization result in more disk & memory consumed or less?

22.Does Normalization require more joins to be written or less?