Съдържание на документа
Exercises: Entity Framework Code First
This document defines the exercise assignments for the "Databases Advanced – Entity Framework" course @ Software University.
Your task is to create table WizardDeposits using the Code First approach. The table should contain the following fields:
Id – Primary Key (number in range [1, 231-1].
FirstName – Text field with max length of 50 symbols.
LastName - Text field with max length of 60 symbols. Required
Notes – Text field with max length of 1000 symbols
Age – Non-negative number. Required
MagicWandCreator – Text field with max length of 100 symbols
MagicWandSize – Number in range [1, 215-1]
DepositGroup - Text field with max length of 20 symbols
DepositStartDate – Date and time field
DepositAmount – Floating point number field
DepositInterest - Floating point number field
DepositCharge - Floating point number field
DepositExpirationDate – Date and time field
IsDepositExpired – Boolean field
Add several records to the database using Entity Framework. Use the following example of wizard deposit creation and addition to the database.
Your task is to create table Users using the Code First approach. The table should contain the following fields:
Id – Primary Key (number in range [1, 231-1])
Username – Text with length between 4 and 30 symbols. Required.
Password – Required field. Text with length between 6 and 50 symbols. Should contain at least:
1 lowercase letter
1 uppercase letter
1 special symbol (!, @, #, $, %, ^, &, *, (, ), _, +, <, >, ?)
E-mail – Required field. Text that is considered to be in format <user>@<host> where:
<user> is a sequence of letters and digits, where '.', '-' and '_' can appear between them (they cannot appear at the beginning or at the end of the sequence).
<host> is a sequence of at least two words, separated by dots '.' (dots cannot appear at the beginning or at the end of the sequence)
ProfilePicture – Image file with size maximum of 1MB
RegisteredOn – Date and time of user registration
LastTimeLoggedIn – Date and time of the last time the user logged in
Age – number in range [1, 120]
IsDeleted – Shows whether the user is deleted or not
Seed some users in the database. Test if validation of the fields works as expected.
Create database and create the following tables using the Code First approach:
Employees (Id, FirstName, LastName, Title, Notes)
Customers (AccountNumber, FirstName, LastName, PhoneNumber, EmergencyName, EmergencyNumber, Notes)
RoomStatus (RoomStatus, Notes)
RoomTypes (RoomType, Notes)
BedTypes (BedType, Notes)
Rooms (RoomNumber, RoomType, BedType, Rate, RoomStatus, Notes)
Payments (Id, PaymentDate, AccountNumber, FirstDateOccupied, LastDateOccupied, TotalDays, AmountCharged, TaxRate, TaxAmount, PaymentTotal, Notes)
Occupancies (Id, DateOccupied, AccountNumber, RoomNumber, RateApplied, PhoneCharge, Notes)
No relationships between tables are required. Make appropriate validations on different fields.
Create database for storing data about sales using the Code First approach. The database should have the following tables:
Product (Id, Name, Quantity, Price)
Customer (Id, Name, Email, CreditCardNumber)
StoreLocation (Id, LocationName)
Sale (Id, ProductId, CustomerId, StoreLocationId, Date)
The relationships between tables are as follows:
Sale has one product and a product can be sold in many sales
Sale has one customer and a customer can participate in many sales
Sale has one store location and one store location can have many sales
Write a seed method that fills the database with sample data (randomly generated). Configure Entity Framework to run the Seed() method after the database is created for the first time (i.e. only if it's empty).
Run the application several times to ensure that it seeds sample data only once.
You can use the following format to design your model classes
Remember to initialize ICollection<Sale> in the constructor of the class to avoid null pointer exceptions.
Congrats! You were hired as a Junior Database App Developer. But before starting to work you were required to provide some documents such as fit note from your GP. So, you go to him to get it. When you tell him, what do you need and what kind of job you are about to start. He told you that he was just looking for someone to make a software to help him managing and keeping data about his patients. He offered you to give you the fit note for free if you help him. You decided that’s a great opportunity to save 20 leva and go out tonight with friends and also you would expand your portfolio with 1 project.
Your task is to design a database using the Code First approach. The GP needs to keep information about his patients. Each patient has first name, last name, address, email, date of birth, picture, information whether he has medical insurance or not and should keep history about all his visitations, diagnoses and prescribed medicaments. Each visitation has date and comments. Each diagnose has name and comments for it. Each medicament has name. Make sure all data is validated before inserting in the database.
Make console based user interface so the doctor can use easily the database.
The console based user interface is a good start point but almost nobody use them nowadays (except BDZ Passenger Services). So, make a nice good looking graphical user interface for the program.
It’s time to modify the database we created in the 2nd task. Now the user should have born town and currently living in town. The town has name and country where is he placed. Migrate the database with the new schema of the table and make sure no data is lost when updating.
Again, it’s modification time this time not so big. Add 2 new properties to the user first name and last name. Also, add one more property FullName that would return the concatenation of first and last name separated by a single space. That property must be generated only when we need it (there is no need to keep it in the database). Migrate the database with the new schema of the table and make sure no data is lost when updating.
Hospital Database Modification
Your GP bragged around in the hospital about the cool software you made for him. And now the hospital administration wants to modify your program so they can use it too. They want to store information about the doctors (name and specialty). Each doctor can perform many visitations and in each visitation, can be performed by only one doctor. Make the necessary changes in the database to satisfy the new needs of the hospital administration. When migrating to the new database schema make sure no data is lost. If you made some user interface (graphical or not), make changes in it be more adequate for the changes.
Make authentication system for doctors. Each doctor should be able to log in with his email and password. When the doctor is logged in he can see only information related about himself (his visitations, the patients he examined, etc.).
Go to task 2 and make a validation attribute [Email] that can be used on string properties. The property should check if the value of the property is valid. One email is valid if in format <user>@<host> where:
<user> is a sequence of letters and digits, where '.', '-' and '_' can appear between them. Examples of valid users: "stephan", "mike03", "s.johnson", "st_steward", "softuni-bulgaria", "12345". Examples of invalid users: ''--123", ".....", "nakov_-", "_steve", ".info".
<host> is a sequence of at least two words, separated by dots '.'. Each word is sequence of letters and can have hyphens '-' between the letters. Examples of hosts: "softuni.bg", "software-university.com", "intoprogramming.info", "mail.s oftuni.org". Examples of invalid hosts: "helloworld", ".unknown.soft.", "invalid-host-", "invalid-".
Examples of valid emails: firstname.lastname@example.org, email@example.com, firstname.lastname@example.org, email@example.com, firstname.lastname@example.org.
Examples of invalid emails: --email@example.com, …@mail.bg, .firstname.lastname@example.org, email@example.com, mike@helloworld, mike@.unknown.soft., s.johnson@invalid-.
Use that attribute on the previous problems to validate any property containing e-mail address.
Make validation attribute [Password] that can be used to validate string properties. The property should check if the value of the property is valid. In the constructor, the password should receive minimum and maximum length of the password. As optional parameters, we should be able to provide whether the password should contain lowercase letter, uppercase letter, digit or special symbol.
Use that attribute on the previous problems to validate any property containing password.
Get Users by Email Provider
Write program that print all usernames and emails of users by given email provider.
No users found with email domain abv.bg
Count Users with Bigger Pictures
Write a program that count the users with pictures bigger than given width.
4 users have profile pictures wider than 120 pixels
1 user has profile picture wider than 921 pixels
No users have profile picture wider than 999 pixels
Remove Inactive Users
Write a program that set IsDeleted field to true for all users that has not been logged in after given date. Print the number of user that has been set as deleted. Then delete all users that have been marked for removal.
12 Oct 2004
10 users have been deleted
10 Jul 2015
1 user has been deleted
01 Nov 2016
No users have been deleted